statementstest.java

mysql5.0 JDBC 驱动 放在glassfish或者tomcat的lib文件夹下就可以了

	    nclob1 = conn2.createNClob();
	    nclob1.setString(1, "aaa");
	    pstmt2.setNClob(1, nclob1);
	    pstmt2.setNClob(2, new StringReader(
	            new String(new char[81921])), 81921); // 10 Full Long Data Packet's chars + 1 char
	    pstmt2.execute();
	    ResultSet rs2 = this.stmt.executeQuery("SELECT c1, c2 FROM testSetNClobServer");
	    rs2.next();
	    assertEquals("aaa", rs2.getString(1));
	    assertEquals(new String(new char[81921]), rs2.getString(2));
	    rs2.close();
	    pstmt2.close();
	    conn2.close();
	}

	/**
	 * Tests for PreparedStatement.setNString()
	 * 
	 * @throws Exception
	 */
	public void testSetNString() throws Exception {
	    // suppose sql_mode don't include "NO_BACKSLASH_ESCAPES"
	    
	    createTable("testSetNString", "(c1 NATIONAL CHARACTER(10), c2 NATIONAL CHARACTER(10), " +
	            "c3 NATIONAL CHARACTER(10)) DEFAULT CHARACTER SET cp932");
	    Properties props1 = new Properties();
	    props1.put("useServerPrepStmts", "false"); // use client-side prepared statement
	    props1.put("useUnicode", "true");
	    props1.put("characterEncoding", "MS932"); // ensure charset isn't utf8 here
	    Connection conn1 = getConnectionWithProps(props1);
	    PreparedStatement pstmt1 = 
	        conn1.prepareStatement("INSERT INTO testSetNString (c1, c2, c3) VALUES (?, ?, ?)");
	    pstmt1.setNString(1, null);
	    pstmt1.setNString(2, "aaa");
	    pstmt1.setNString(3, "\'aaa\'");
	    pstmt1.execute();
	    ResultSet rs1 = this.stmt.executeQuery("SELECT c1, c2, c3 FROM testSetNString");
	    rs1.next();
	    assertEquals(null, rs1.getString(1));
	    assertEquals("aaa", rs1.getString(2));
	    assertEquals("\'aaa\'", rs1.getString(3));
	    rs1.close();
	    pstmt1.close();
	    conn1.close();
	    
	    createTable("testSetNString", "(c1 NATIONAL CHARACTER(10), c2 NATIONAL CHARACTER(10), " +
	    "c3 NATIONAL CHARACTER(10)) DEFAULT CHARACTER SET cp932");
	    Properties props2 = new Properties();
	    props2.put("useServerPrepStmts", "false"); // use client-side prepared statement
	    props2.put("useUnicode", "true");
	    props2.put("characterEncoding", "UTF-8"); // ensure charset is utf8 here
	    Connection conn2 = getConnectionWithProps(props2);
	    PreparedStatement pstmt2 = 
	        conn2.prepareStatement("INSERT INTO testSetNString (c1, c2, c3) VALUES (?, ?, ?)");
	    pstmt2.setNString(1, null);
	    pstmt2.setNString(2, "aaa");
	    pstmt2.setNString(3, "\'aaa\'");
	    pstmt2.execute();
	    ResultSet rs2 = this.stmt.executeQuery("SELECT c1, c2, c3 FROM testSetNString");
	    rs2.next();
	    assertEquals(null, rs2.getString(1));
	    assertEquals("aaa", rs2.getString(2));
	    assertEquals("\'aaa\'", rs2.getString(3));
	    rs2.close();
	    pstmt2.close();
	    conn2.close();
	}

	/**
	 * Tests for ServerPreparedStatement.setNString()
	 * 
	 * @throws Exception
	 */
	public void testSetNStringServer() throws Exception {
	    createTable("testSetNStringServer", "(c1 NATIONAL CHARACTER(10))");
	    Properties props1 = new Properties();
	    props1.put("useServerPrepStmts", "true"); // use server-side prepared statement
	    props1.put("useUnicode", "true");
	    props1.put("characterEncoding", "latin1"); // ensure charset isn't utf8 here
	    Connection conn1 = getConnectionWithProps(props1);
	    PreparedStatement pstmt1 = 
	        conn1.prepareStatement("INSERT INTO testSetNStringServer (c1) VALUES (?)");
	    try {
	        pstmt1.setNString(1, "aaa");
	        fail();
	    } catch (SQLException e) {
	        // ok
	        assertEquals("Can not call setNString() when connection character set isn't UTF-8",
	            e.getMessage());  
	    }
	    pstmt1.close();
	    conn1.close();
	    
	    createTable("testSetNStringServer", "(c1 NATIONAL CHARACTER(10))");
	    Properties props2 = new Properties();
	    props2.put("useServerPrepStmts", "true"); // use server-side prepared statement
	    props2.put("useUnicode", "true");
	    props2.put("characterEncoding", "UTF-8"); // ensure charset is utf8 here
	    Connection conn2 = getConnectionWithProps(props2);
	    PreparedStatement pstmt2 = 
	        conn2.prepareStatement("INSERT INTO testSetNStringServer (c1) VALUES (?)");
	    pstmt2.setNString(1, "\'aaa\'");
	    pstmt2.execute();
	    ResultSet rs2 = this.stmt.executeQuery("SELECT c1 FROM testSetNStringServer");
	    rs2.next();
	    assertEquals("\'aaa\'", rs2.getString(1));
	    rs2.close();
	    pstmt2.close();
	    conn2.close();
	}

	/**
	 * Tests for ResultSet.updateNCharacterStream()
	 * 
	 * @throws Exception
	 */
	public void testUpdateNCharacterStream() throws Exception {
	    createTable("testUpdateNCharacterStream", 
	            "(c1 CHAR(10) PRIMARY KEY, c2 NATIONAL CHARACTER(10)) default character set sjis");
	    Properties props1 = new Properties();
	    props1.put("useServerPrepStmts", "true"); // use server-side prepared statement
	    props1.put("characterEncoding", "UTF-8"); // ensure charset isn't utf8 here
	    Connection conn1 = getConnectionWithProps(props1);
	    PreparedStatement pstmt1 = conn1.prepareStatement(
	            "INSERT INTO testUpdateNCharacterStream (c1, c2) VALUES (?, ?)");
	    pstmt1.setString(1, "1");
	    pstmt1.setNCharacterStream(2, new StringReader("aaa"), 3);
	    pstmt1.execute();
	    Statement stmt1 = conn1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
	    ResultSet rs1 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNCharacterStream");
	    rs1.next();
	    rs1.updateNCharacterStream("c2", new StringReader("bbb"), 3);
	    rs1.updateRow();
	    rs1.moveToInsertRow();
	    rs1.updateString("c1", "2");
	    rs1.updateNCharacterStream("c2", new StringReader("ccc"), 3);
	    rs1.insertRow();
	    ResultSet rs2 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNCharacterStream");
	    rs2.next();
	    assertEquals("1", rs2.getString("c1"));
	    assertEquals("bbb", rs2.getNString("c2"));
	    rs2.next();
	    assertEquals("2", rs2.getString("c1"));
	    assertEquals("ccc", rs2.getNString("c2"));
	    pstmt1.close();
	    stmt1.close();
	    conn1.close();
	    
	    createTable("testUpdateNCharacterStream", 
	            "(c1 CHAR(10) PRIMARY KEY, c2 CHAR(10)) default character set sjis"); // sjis field
	    Properties props2 = new Properties();
	    props2.put("useServerPrepStmts", "true"); // use server-side prepared statement
	    props2.put("characterEncoding", "SJIS"); // ensure charset isn't utf8 here
	    Connection conn2 = getConnectionWithProps(props2);
	    PreparedStatement pstmt2 = conn2.prepareStatement(
	            "INSERT INTO testUpdateNCharacterStream (c1, c2) VALUES (?, ?)");
	    pstmt2.setString(1, "1");
	    pstmt2.setString(2, "aaa");
	    pstmt2.execute();
	    Statement stmt2 = conn2.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
	    ResultSet rs3 = stmt2.executeQuery("SELECT c1, c2 FROM testUpdateNCharacterStream");
	    rs3.next();
	    try {
	        rs3.updateNCharacterStream("c2", new StringReader("bbb"), 3); // field's charset isn't utf8
	        fail();
	    } catch (SQLException ex) {
	        assertEquals("Can not call updateNCharacterStream() when field's character set isn't UTF-8",
	                ex.getMessage());
	    }
	    rs3.close();
	    pstmt2.close();
	    stmt2.close();
	    conn2.close();  
	}

	/**
	 * Tests for ResultSet.updateNClob()
	 * 
	 * @throws Exception
	 */
	public void testUpdateNClob() throws Exception {
	    createTable("testUpdateNChlob", 
	            "(c1 CHAR(10) PRIMARY KEY, c2 NATIONAL CHARACTER(10)) default character set sjis");
	    Properties props1 = new Properties();
	    props1.put("useServerPrepStmts", "true"); // use server-side prepared statement
	    props1.put("characterEncoding", "UTF-8"); // ensure charset isn't utf8 here
	    Connection conn1 = getConnectionWithProps(props1);
	    PreparedStatement pstmt1 = conn1.prepareStatement(
	            "INSERT INTO testUpdateNChlob (c1, c2) VALUES (?, ?)");
	    pstmt1.setString(1, "1");
	    NClob nClob1 = conn1.createNClob();
	    nClob1.setString(1, "aaa");
	    pstmt1.setNClob(2, nClob1);
	    pstmt1.execute();
	    Statement stmt1 = conn1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
	    ResultSet rs1 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNChlob");
	    rs1.next();
	    NClob nClob2 = conn1.createNClob();
	    nClob2.setString(1, "bbb");
	    rs1.updateNClob("c2", nClob2);
	    rs1.updateRow();
	    rs1.moveToInsertRow();
	    rs1.updateString("c1", "2");
	    NClob nClob3 = conn1.createNClob();
	    nClob3.setString(1, "ccc");
	    rs1.updateNClob("c2", nClob3);
	    rs1.insertRow();
	    ResultSet rs2 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNChlob");
	    rs2.next();
	    assertEquals("1", rs2.getString("c1"));
	    assertEquals("bbb", rs2.getNString("c2"));
	    rs2.next();
	    assertEquals("2", rs2.getString("c1"));
	    assertEquals("ccc", rs2.getNString("c2"));
	    pstmt1.close();
	    stmt1.close();
	    conn1.close();
	    
	    createTable("testUpdateNChlob", 
	            "(c1 CHAR(10) PRIMARY KEY, c2 CHAR(10)) default character set sjis"); // sjis field
	    Properties props2 = new Properties();
	    props2.put("useServerPrepStmts", "true"); // use server-side prepared statement
	    props2.put("characterEncoding", "SJIS"); // ensure charset isn't utf8 here
	    Connection conn2 = getConnectionWithProps(props2);
	    PreparedStatement pstmt2 = conn2.prepareStatement(
	            "INSERT INTO testUpdateNChlob (c1, c2) VALUES (?, ?)");
	    pstmt2.setString(1, "1");
	    pstmt2.setString(2, "aaa");
	    pstmt2.execute();
	    Statement stmt2 = conn2.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
	    ResultSet rs3 = stmt2.executeQuery("SELECT c1, c2 FROM testUpdateNChlob");
	    rs3.next();
	    NClob nClob4 = conn2.createNClob();
	    nClob4.setString(1, "bbb");
	    try {
	        rs3.updateNClob("c2", nClob4); // field's charset isn't utf8
	        fail();
	    } catch (SQLException ex) {
	        assertEquals("Can not call updateNClob() when field's character set isn't UTF-8",
	                ex.getMessage());
	    }
	    rs3.close();
	    pstmt2.close();
	    stmt2.close();
	    conn2.close();  
	}

	/**
	 * Tests for ResultSet.updateNString()
	 * 
	 * @throws Exception
	 */
	public void testUpdateNString() throws Exception {
	    createTable("testUpdateNString", 
	            "(c1 CHAR(10) PRIMARY KEY, c2 NATIONAL CHARACTER(10)) default character set sjis");
	    Properties props1 = new Properties();
	    props1.put("useServerPrepStmts", "true"); // use server-side prepared statement
	    props1.put("characterEncoding", "UTF-8"); // ensure charset is utf8 here
	    Connection conn1 = getConnectionWithProps(props1);
	    PreparedStatement pstmt1 = conn1.prepareStatement(
	            "INSERT INTO testUpdateNString (c1, c2) VALUES (?, ?)");
	    pstmt1.setString(1, "1");
	    pstmt1.setNString(2, "aaa");
	    pstmt1.execute();
	    Statement stmt1 = conn1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
	    ResultSet rs1 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNString");
	    rs1.next();
	    rs1.updateNString("c2", "bbb");
	    rs1.updateRow();
	    rs1.moveToInsertRow();
	    rs1.updateString("c1", "2");
	    rs1.updateNString("c2", "ccc");
	    rs1.insertRow();
	    ResultSet rs2 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNString");
	    rs2.next();
	    assertEquals("1", rs2.getString("c1"));
	    assertEquals("bbb", rs2.getNString("c2"));
	    rs2.next();
	    assertEquals("2", rs2.getString("c1"));
	    assertEquals("ccc", rs2.getNString("c2"));
	    pstmt1.close();
	    stmt1.close();
	    conn1.close();
	    
	    createTable("testUpdateNString", 
	            "(c1 CHAR(10) PRIMARY KEY, c2 CHAR(10)) default character set sjis"); // sjis field
	    Properties props2 = new Properties();
	    props2.put("useServerPrepStmts", "true"); // use server-side prepared statement
	    props2.put("characterEncoding", "SJIS"); // ensure charset isn't utf8 here
	    Connection conn2 = getConnectionWithProps(props2);
	    PreparedStatement pstmt2 = conn2.prepareStatement(
	            "INSERT INTO testUpdateNString (c1, c2) VALUES (?, ?)");
	    pstmt2.setString(1, "1");
	    pstmt2.setString(2, "aaa");
	    pstmt2.execute();
	    Statement stmt2 = conn2.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
	    ResultSet rs3 = stmt2.executeQuery("SELECT c1, c2 FROM testUpdateNString");
	    rs3.next();
	    try {
	        rs3.updateNString("c2", "bbb"); // field's charset isn't utf8
	        fail();
	    } catch (SQLException ex) {
	        assertEquals("Can not call updateNString() when field's character set isn't UTF-8",
	                ex.getMessage());
	    }
	    rs3.close();
	    pstmt2.close();
	    stmt2.close();
	    conn2.close();      
	}
}