JDBCTemplate在hsqldb数据库中的应用

public

class 
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

    public

void 
test(){

        insert();

        update();

        delete();

        select();

        testPpreparedStatement();

        testPreparedStatement2();

        testResultSet();

        testResultSet2();

        testResultSet3();

        testCallableStatementCreator1();

        testCallableStatementCreator3();

    }

 

    /**

     *
 插入数据

     */

    private

void 
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));

    }

     

    /**

     *
 更新数据

     */

    private

void 
update(){

        String
 sql = "select
 count(*) from test where name='name3'";

        jdbcTemplate.update("update
 test set name='name3' where name=?",new

Object[]{"name2"});

        assertEquals(1,
 jdbcTemplate.queryForInt(sql));

        System.out.println("更新的记录数为："+jdbcTemplate.queryForInt(sql));

    }

     

    /**

     *
 删除数据

     */

    private

void 
delete(){

        String
 sql = "select
 count(*) from test";

        jdbcTemplate.update("delete
 from test where name =?",new

Object[]{"name1"});

        assertEquals(1,
 jdbcTemplate.queryForInt(sql));

        System.out.println("删除的记录数为："+jdbcTemplate.queryForInt(sql));

         

    }

 

    /**

     *
 查询数据(利用回调函数)

     */

    private

void 
select(){

        String
 sql = "select
 count(*) from test";

        jdbcTemplate.query("select
 * from test",
new

RowCallbackHandler(){

 

            @Override

            public

void 
processRow(ResultSet rs) throws

SQLException {

                int

id = rs.getInt("id");

                String
 name = rs.getString("name");

                System.out.println("id="+id+","+"name="+name);

                 

            }});

        System.out.println("查询的数据："+jdbcTemplate.queryForInt(sql));

    }

 

    /**

     *
 预编译语句及存储过程创建回调

     */

    private

void 
testPpreparedStatement(){

        int

count = jdbcTemplate.execute(new

PreparedStatementCreator() {

            @Override

            public

PreparedStatement createPreparedStatement(Connection conn)

                    throws

SQLException {

                return

conn.prepareStatement("select
 count(*) from test");

            }

        },
new

PreparedStatementCallback<Integer>(){

 

            @Override

            public

Integer doInPreparedStatement(PreparedStatement pstmt)

                    throws

SQLException, DataAccessException {

                pstmt.execute();

                ResultSet
 rs = pstmt.executeQuery();

                rs.next();

                return

rs.getInt(1);

            }  

        });

        assertEquals(1,
 count);

        System.out.println("预编译语句count=："+count);

    }

 

    /**

     *
 预编译语句设值回调使用(带参数)

     */

    private

void 
testPreparedStatement2(){

        String
 sql = "insert
 into test(name) values(?)";

        int

count = jdbcTemplate.update(sql,new

PreparedStatementSetter(){

 

            @Override

            public

void 
setValues(PreparedStatement ps) throws

SQLException {

                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, new

Object[]{"name4"});

        assertEquals(1,
 count);

        System.out.println("删除后的数据条目："+count);

    }

 

    /**

     *
 结果集处理回调(RowMapper<Map>接口，以Map的方式返回结果集)

     */

    private

void 
testResultSet(){

        jdbcTemplate.update("insert
 into test(name) values('name5')"); 

        String
 listSql = "select
 * from test"; 

        List
 list = jdbcTemplate.query(listSql, new

RowMapper<Map>() {  

            @Override 

            public

Map mapRow(ResultSet rs, int

rowNum) throws

SQLException {  

                Map
 row = new

HashMap();  

                row.put(rs.getInt("id"),
 rs.getString("name")); 

                return

row;  

        }}); 

        jdbcTemplate.update("delete
 from test where name='name5'");

        assertEquals(2,
 list.size());

        System.out.println("当前的list结果为"+list.get(0)+","+list.get(1));

    }

 

    /**

     *
 结果集处理回调(RowCallbackHandler接口，将结果集转换为需要的形式)

     */

    private

void 
testResultSet2(){

        jdbcTemplate.update("insert
 into test(name) values(?)",new

Object[]{"name5"});

        String
 sql = "select
 * from test";

        final

List list = new

ArrayList();

        jdbcTemplate.query(sql,new

RowCallbackHandler(){

 

            @Override

            public

void 
processRow(ResultSet rs) throws

SQLException {

                Map
 map = new

HashMap();

                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=?",
new

Object[]{"name5"});

    }

 

    /**

     *
 结果集处理回调(ResultSetExtractor接口，提供给用户整个结果集，让用户决定如何处理该结果集)

     */

    private

void 
testResultSet3(){

        jdbcTemplate.update("insert
 into test(name) values(?)",new

Object[]{"name5"});

        String
 sql = "select
 * from test";

        List
 result = jdbcTemplate.query(sql,new

ResultSetExtractor<List>(){

            @Override

            public

List extractData(ResultSet rs) throws

SQLException,

                    DataAccessException
 {

                List
 list = new

ArrayList();

                while(rs.next()){

                    Map
 map = new

HashMap();

                    map.put(rs.getInt("id"),rs.getString("name"));

                    list.add(map);

                }

                return

list;

            }

        });

        assertEquals(2,
 result.size());

        System.out.println("当前的数组1结果为"+result.get(0)+","+result.get(1));

        jdbcTemplate.update("delete
 from test where name=?",new

Object[]{"name5"});

    }

 

    /**

     *
 调用函数(利用回调函数)

     */

    public

void 
testCallableStatementCreator1() {  

        final

String callFunctionSql = "{call
 FUNCTION_TEST(?)}"; 

        List<SqlParameter>
 params = new

ArrayList<SqlParameter>(); 

        //设置函数参数

        params.add(new

SqlParameter(Types.VARCHAR));  

        //设置函数返回结果集

        params.add(new

SqlReturnResultSet("result",
new

ResultSetExtractor<Integer>(){

            @Override

            public

Integer extractData(ResultSet rs) throws

SQLException,

                    DataAccessException
 {

                while(rs.next()){

                    return

rs.getInt(1);

                }

                return

0;

            }}));

        //获取结果   

        Map<String,
 Object> outValues = jdbcTemplate.call(new

CallableStatementCreator() {

             

            @Override

            public

CallableStatement createCallableStatement(Connection conn)

                    throws

SQLException {

                CallableStatement
 cstm = conn.prepareCall(callFunctionSql);

                cstm.setString(1,
"test");

                return

cstm;

            }

        },
 params);  

        System.out.println("函数调用成功！");

        System.out.println(outValues.get("result"));

        Assert.assertEquals(4,
 outValues.get("result")); 

    }

 

    /**

     *
 MYSQL的函数调用（和之前的类似）

     */

    public

void 
testCallableStatementCreator2() {  

        JdbcTemplate
 mysqlJdbcTemplate = new

JdbcTemplate(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 …}  

        final

String callFunctionSql = "{?=
 call FUNCTION_TEST(?)}"; 

        //4.定义参数 

        List<SqlParameter>
 params = new

ArrayList<SqlParameter>();  

        params.add(new

SqlOutParameter("result",
 Types.INTEGER));  

        params.add(new

SqlParameter("str",
 Types.VARCHAR));  

        Map<String,
 Object> outValues = mysqlJdbcTemplate.call(  

        new

CallableStatementCreator() {  

            @Override 

            public

CallableStatement createCallableStatement(Connection conn) throws

SQLException {  

              CallableStatement
 cstmt = conn.prepareCall(callFunctionSql);  

              cstmt.registerOutParameter(1,
 Types.INTEGER);  

              cstmt.setString(2,
"test"); 

                return

cstmt;  

            }},
 params);  

           Assert.assertEquals(4,
 outValues.get("result")); 

        } 

    public

DataSource getMysqlDataSource() {  

        String
 url = "jdbc:mysql://localhost:3306/test"; 

        DriverManagerDataSource
 dataSource =  

            new

DriverManagerDataSource(url, "root",
"");    
 dataSource.setDriverClassName("com.mysql.jdbc.Driver"); 

        return

dataSource;  

    }

     

    /**

     *
 调用存储过程(利用回调函数)

     */

    public

void 
testCallableStatementCreator3() {  

        final

String callProcedureSql = "{call
 PROCEDURE_TEST(?, ?)}"; 

        List<SqlParameter>
 params = new

ArrayList<SqlParameter>();

        //定义数组输出参数

        params.add(new

SqlInOutParameter("inOutName",
 Types.VARCHAR));  

        //定义存储过程参数

        params.add(new

SqlOutParameter("outId",
 Types.INTEGER));  

        Map<String,
 Object> outValues = jdbcTemplate.call(  

          new

CallableStatementCreator() {  

            @Override 

            public

CallableStatement createCallableStatement(Connection conn) throws

SQLException {  

              CallableStatement
 cstmt = conn.prepareCall(callProcedureSql);  

              cstmt.registerOutParameter(1,
 Types.VARCHAR);  

              cstmt.registerOutParameter(2,
 Types.INTEGER);  

              cstmt.setString(1,
"test"); 

              return

cstmt;  

        }},
 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

    public

void 
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("表删除成功！");

    }

}