publicclass JDBCTemplateTest { //初始化JDBCTemplate private static JdbcTemplate jdbcTemplate; @BeforeClass public static void setUpClass() { //利用内存来作为数据的存储源 String url = "jdbc:hsqldb:mem:test"; String username = "sa"; String password = ""; //获取数据源 DriverManagerDataSource dataSource = new DriverManagerDataSource(url, username, password); //设置驱动 dataSource.setDriverClassName("org.hsqldb.jdbcDriver"); jdbcTemplate = new JdbcTemplate(dataSource); } @Before public void setUp(){ //建表 String createTableSql = "create memory table test" + "(id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, " + "name varchar(100))"; //execute一般用来执行DDL jdbcTemplate.execute(createTableSql); System.out.println("表创建成功"); //建函数 String createHsqldbFunctionSql = "CREATE FUNCTION FUNCTION_TEST(str CHAR(100)) " "returns INT begin atomic return length(str);end"; jdbcTemplate.update(createHsqldbFunctionSql); System.out.println("函数创建成功"); //创建存储过程 String createHsqldbProcedureSql = "CREATE PROCEDURE PROCEDURE_TEST" + "(INOUT inOutName VARCHAR(100), OUT outId INT) " + "MODIFIES SQL DATA " + "BEGIN ATOMIC " + " insert into test(name) values (inOutName); " + " SET outId = IDENTITY(); " + " SET inOutName = 'Hello,' + inOutName; " + "END"; jdbcTemplate.update(createHsqldbProcedureSql); System.out.println("存储过程创建成功！"); } @Test publicvoid test(){ insert(); update(); delete(); select(); testPpreparedStatement(); testPreparedStatement2(); testResultSet(); testResultSet2(); testResultSet3(); testCallableStatementCreator1(); testCallableStatementCreator3(); } /** * 插入数据 */ privatevoid insert(){ String sql = "select count(*) from test"; jdbcTemplate.update("insert into test(name) values('name1')"); jdbcTemplate.update("insert into test(name) values('name2')"); assertEquals(2, jdbcTemplate.queryForInt(sql)); System.out.println("插入记录数为："+jdbcTemplate.queryForInt(sql)); } /** * 更新数据 */ privatevoid update(){ String sql = "select count(*) from test where name='name3'"; jdbcTemplate.update("update test set name='name3' where name=?",newObject[]{"name2"}); assertEquals(1, jdbcTemplate.queryForInt(sql)); System.out.println("更新的记录数为："+jdbcTemplate.queryForInt(sql)); } /** * 删除数据 */ privatevoid delete(){ String sql = "select count(*) from test"; jdbcTemplate.update("delete from test where name =?",newObject[]{"name1"}); assertEquals(1, jdbcTemplate.queryForInt(sql)); System.out.println("删除的记录数为："+jdbcTemplate.queryForInt(sql)); } /** * 查询数据(利用回调函数) */ privatevoid select(){ String sql = "select count(*) from test"; jdbcTemplate.query("select * from test",newRowCallbackHandler(){ @Override publicvoid processRow(ResultSet rs) throwsSQLException { intid = rs.getInt("id"); String name = rs.getString("name"); System.out.println("id="+id+","+"name="+name); }}); System.out.println("查询的数据："+jdbcTemplate.queryForInt(sql)); } /** * 预编译语句及存储过程创建回调 */ privatevoid testPpreparedStatement(){ intcount = jdbcTemplate.execute(newPreparedStatementCreator() { @Override publicPreparedStatement createPreparedStatement(Connection conn) throwsSQLException { returnconn.prepareStatement("select count(*) from test"); } },newPreparedStatementCallback<Integer>(){ @Override publicInteger doInPreparedStatement(PreparedStatement pstmt) throwsSQLException, DataAccessException { pstmt.execute(); ResultSet rs = pstmt.executeQuery(); rs.next(); returnrs.getInt(1); } }); assertEquals(1, count); System.out.println("预编译语句count=："+count); } /** * 预编译语句设值回调使用(带参数) */ privatevoid testPreparedStatement2(){ String sql = "insert into test(name) values(?)"; intcount = jdbcTemplate.update(sql,newPreparedStatementSetter(){ @Override publicvoid setValues(PreparedStatement ps) throwsSQLException { ps.setObject(1,"name4"); } }); assertEquals(1, count); System.out.println("当前的数据条目："+count); //优先使用update(sql,args) String sqldelete = "delete from test where name =?"; count = jdbcTemplate.update(sqldelete, newObject[]{"name4"}); assertEquals(1, count); System.out.println("删除后的数据条目："+count); } /** * 结果集处理回调(RowMapper<Map>接口，以Map的方式返回结果集) */ privatevoid testResultSet(){ jdbcTemplate.update("insert into test(name) values('name5')"); String listSql = "select * from test"; List list = jdbcTemplate.query(listSql, newRowMapper<Map>() { @Override publicMap mapRow(ResultSet rs, introwNum) throwsSQLException { Map row = newHashMap(); row.put(rs.getInt("id"), rs.getString("name")); returnrow; }}); jdbcTemplate.update("delete from test where name='name5'"); assertEquals(2, list.size()); System.out.println("当前的list结果为"+list.get(0)+","+list.get(1)); } /** * 结果集处理回调(RowCallbackHandler接口，将结果集转换为需要的形式) */ privatevoid testResultSet2(){ jdbcTemplate.update("insert into test(name) values(?)",newObject[]{"name5"}); String sql = "select * from test"; finalList list = newArrayList(); jdbcTemplate.query(sql,newRowCallbackHandler(){ @Override publicvoid processRow(ResultSet rs) throwsSQLException { Map map = newHashMap(); map.put(rs.getInt("id"), rs.getString("name")); list.add(map); } }); assertEquals(2, list.size()); System.out.println("当前的数组结果为"+list.get(0)+","+list.get(1)); jdbcTemplate.update("delete from test where name=?",newObject[]{"name5"}); } /** * 结果集处理回调(ResultSetExtractor接口，提供给用户整个结果集，让用户决定如何处理该结果集) */ privatevoid testResultSet3(){ jdbcTemplate.update("insert into test(name) values(?)",newObject[]{"name5"}); String sql = "select * from test"; List result = jdbcTemplate.query(sql,newResultSetExtractor<List>(){ @Override publicList extractData(ResultSet rs) throwsSQLException, DataAccessException { List list = newArrayList(); while(rs.next()){ Map map = newHashMap(); map.put(rs.getInt("id"),rs.getString("name")); list.add(map); } returnlist; } }); assertEquals(2, result.size()); System.out.println("当前的数组1结果为"+result.get(0)+","+result.get(1)); jdbcTemplate.update("delete from test where name=?",newObject[]{"name5"}); } /** * 调用函数(利用回调函数) */ publicvoid testCallableStatementCreator1() { finalString callFunctionSql = "{call FUNCTION_TEST(?)}"; List<SqlParameter> params = newArrayList<SqlParameter>(); //设置函数参数 params.add(newSqlParameter(Types.VARCHAR)); //设置函数返回结果集 params.add(newSqlReturnResultSet("result",newResultSetExtractor<Integer>(){ @Override publicInteger extractData(ResultSet rs) throwsSQLException, DataAccessException { while(rs.next()){ returnrs.getInt(1); } return0; }})); //获取结果 Map<String, Object> outValues = jdbcTemplate.call(newCallableStatementCreator() { @Override publicCallableStatement createCallableStatement(Connection conn) throwsSQLException { CallableStatement cstm = conn.prepareCall(callFunctionSql); cstm.setString(1,"test"); returncstm; } }, params); System.out.println("函数调用成功！"); System.out.println(outValues.get("result")); Assert.assertEquals(4, outValues.get("result")); } /** * MYSQL的函数调用（和之前的类似） */ publicvoid testCallableStatementCreator2() { JdbcTemplate mysqlJdbcTemplate = newJdbcTemplate(getMysqlDataSource()); //2.创建自定义函数 String createFunctionSql = "CREATE FUNCTION FUNCTION_TEST(str VARCHAR(100)) " + "returns INT return LENGTH(str)"; String dropFunctionSql = "DROP FUNCTION IF EXISTS FUNCTION_TEST"; mysqlJdbcTemplate.update(dropFunctionSql); mysqlJdbcTemplate.update(createFunctionSql); //3.准备sql,mysql支持{?= call …} finalString callFunctionSql = "{?= call FUNCTION_TEST(?)}"; //4.定义参数 List<SqlParameter> params = newArrayList<SqlParameter>(); params.add(newSqlOutParameter("result", Types.INTEGER)); params.add(newSqlParameter("str", Types.VARCHAR)); Map<String, Object> outValues = mysqlJdbcTemplate.call( newCallableStatementCreator() { @Override publicCallableStatement createCallableStatement(Connection conn) throwsSQLException { CallableStatement cstmt = conn.prepareCall(callFunctionSql); cstmt.registerOutParameter(1, Types.INTEGER); cstmt.setString(2,"test"); returncstmt; }}, params); Assert.assertEquals(4, outValues.get("result")); } publicDataSource getMysqlDataSource() { String url = "jdbc:mysql://localhost:3306/test"; DriverManagerDataSource dataSource = newDriverManagerDataSource(url, "root",""); dataSource.setDriverClassName("com.mysql.jdbc.Driver"); returndataSource; } /** * 调用存储过程(利用回调函数) */ publicvoid testCallableStatementCreator3() { finalString callProcedureSql = "{call PROCEDURE_TEST(?, ?)}"; List<SqlParameter> params = newArrayList<SqlParameter>(); //定义数组输出参数 params.add(newSqlInOutParameter("inOutName", Types.VARCHAR)); //定义存储过程参数 params.add(newSqlOutParameter("outId", Types.INTEGER)); Map<String, Object> outValues = jdbcTemplate.call( newCallableStatementCreator() { @Override publicCallableStatement createCallableStatement(Connection conn) throwsSQLException { CallableStatement cstmt = conn.prepareCall(callProcedureSql); cstmt.registerOutParameter(1, Types.VARCHAR); cstmt.registerOutParameter(2, Types.INTEGER); cstmt.setString(1,"test"); returncstmt; }}, params); Assert.assertEquals("Hello,test", outValues.get("inOutName")); Assert.assertEquals(6, outValues.get("outId")); } /* public void test() { //1.声明SQL String sql = "select * from INFORMATION_SCHEMA.SYSTEM_TABLES"; //2.执行查询 jdbcTemplate.query(sql, new RowCallbackHandler(){ @Override public void processRow(ResultSet rs) throws SQLException { //2.处理结果集 String value = rs.getString("TABLE_NAME"); System.out.println("Column TABLENAME:" + value); }}); } */ @After publicvoid setDown(){ jdbcTemplate.execute("drop function FUNCTION_TEST"); System.out.println("函数删除成功！"); jdbcTemplate.execute("DROP PROCEDURE PROCEDURE_TEST"); System.out.println("存储过程删除成功！"); String dropTableSQL = "drop table test"; jdbcTemplate.execute(dropTableSQL); System.out.println("表删除成功！"); }}
