您的位置：首页 > 博客中心 > 数据库 >

(转)JDBC模板类。

时间：2022-03-10 17:34

//JDBC抽象框架模块
org.springframework.jdbc-3.0.5.RELEASE.jar
//Spring事务管理及一致的DAO访问及非检查异常模块
org.springframework.transaction-3.0.5.RELEASE.jar
//hsqldb驱动，hsqldb是一个开源的Java实现数据库，请下载hsqldb2.0.0+版本
hsqldb.jar

package cn.javass.spring.chapter7;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
public class JdbcTemplateTest {
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);
}
@Test
public void test() {
//1.声明SQL
String sql = "select * from INFORMATION_SCHEMA.SYSTEM_TABLES";
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);
}
});
}
}

//代码片段(cn.javass.spring.chapter7.JdbcTemplateTest)
@Before
public void setUp() {
String createTableSql = "create memory table test" + "(id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, " + "name varchar(100))";
jdbcTemplate.update(createTableSql);
}
@After
public void tearDown() {
String dropTableSql = "drop table test";
jdbcTemplate.execute(dropTableSql);
}

@Test
public void testCURD() {
insert();
delete();
update();
select();
}

private void insert() {
jdbcTemplate.update("insert into test(name) values(‘name1‘)");
jdbcTemplate.update("insert into test(name) values(‘name2‘)");
Assert.assertEquals(2, jdbcTemplate.queryForInt("select count(*) from test"));
}

private void delete() {
jdbcTemplate.update("delete from test where name=?", new Object[]{"name2"});
Assert.assertEquals(1, jdbcTemplate.queryForInt("select count(*) from test"));
}

private void update() {
jdbcTemplate.update("update test set name=‘name3‘ where name=?", new Object[]{"name1"});
Assert.assertEquals(1, jdbcTemplate.queryForInt("select count(*) from test where name=‘name3‘"));
}

private void select() {
jdbcTemplate.query("select * from test", new RowCallbackHandler(){
@Override
public void processRow(ResultSet rs) throws SQLException {
System.out.print("====id:" + rs.getInt("id"));
System.out.println(",name:" + rs.getString("name"));
}
});
}

@Test
public void testPpreparedStatement1() {
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.getResultSet();
rs.next();
return rs.getInt(1);
}});
Assert.assertEquals(0, count);
}

@Test
public void testPreparedStatement2() {
String insertSql = "insert into test(name) values (?)";
int count = jdbcTemplate.update(insertSql, new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement pstmt) throws SQLException {
pstmt.setObject(1, "name4");
}});
Assert.assertEquals(1, count);
String deleteSql = "delete from test where name=?";
count = jdbcTemplate.update(deleteSql, new Object[] {"name4"});
Assert.assertEquals(1, count);
}

@Test
public void testResultSet1() {
jdbcTemplate.update("insert into test(name) values(‘name5‘)");
String listSql = "select * from test";
List result = 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;
}});
Assert.assertEquals(1, result.size());
jdbcTemplate.update("delete from test where name=‘name5‘");
}

@Test
public void testResultSet2() {
jdbcTemplate.update("insert into test(name) values(‘name5‘)");
String listSql = "select * from test";
final List result = new ArrayList();
jdbcTemplate.query(listSql, new RowCallbackHandler() {
@Override
public void processRow(ResultSet rs) throws SQLException {
Map row = new HashMap();
row.put(rs.getInt("id"), rs.getString("name"));
result.add(row);
}});
Assert.assertEquals(1, result.size());
jdbcTemplate.update("delete from test where name=‘name5‘");
}

@Test
public void testResultSet3() {
jdbcTemplate.update("insert into test(name) values(‘name5‘)");
String listSql = "select * from test";
List result = jdbcTemplate.query(listSql, new ResultSetExtractor<List>() {
@Override
public List extractData(ResultSet rs)
throws SQLException, DataAccessException {
List result = new ArrayList();
while(rs.next()) {
Map row = new HashMap();
row.put(rs.getInt("id"), rs.getString("name"));
result.add(row);
}
return result;
}});
Assert.assertEquals(0, result.size());
jdbcTemplate.update("delete from test where name=‘name5‘");
}

//1.查询一行数据并返回int型结果
jdbcTemplate.queryForInt("select count(*) from test");
//2. 查询一行数据并将该行数据转换为Map返回
jdbcTemplate.queryForMap("select * from test where name=‘name5‘");
//3.查询一行任何类型的数据，最后一个参数指定返回结果类型
jdbcTemplate.queryForObject("select count(*) from test", Integer.class);
//4.查询一批数据，默认将每行数据转换为Map
jdbcTemplate.queryForList("select * from test");
//5.只查询一列数据列表，列类型是String类型，列名字是name
jdbcTemplate.queryForList("
select name from test where name=?", new Object[]{"name5"}, String.class);
//6.查询一批数据，返回为SqlRowSet，类似于ResultSet，但不再绑定到连接上
SqlRowSet rs = jdbcTemplate.queryForRowSet("select * from test");

@Before
public void setUp() {
String createTableSql = "create memory table test" +
"(id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, " +
"name varchar(100))";
jdbcTemplate.update(createTableSql);
String createHsqldbFunctionSql =
"CREATE FUNCTION FUNCTION_TEST(str CHAR(100)) " +
"returns INT begin atomic return length(str);end";
jdbcTemplate.update(createHsqldbFunctionSql);
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.execute(createHsqldbProcedureSql);
}

@After
public void tearDown() {
jdbcTemplate.execute("DROP FUNCTION FUNCTION_TEST");
jdbcTemplate.execute("DROP PROCEDURE PROCEDURE_TEST");
String dropTableSql = "drop table test";
jdbcTemplate.execute(dropTableSql);
}

@Test
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 cstmt = conn.prepareCall(callFunctionSql);
cstmt.setString(1, "test");
return cstmt;
}}, params);
Assert.assertEquals(4, outValues.get("result"));
}

@Test
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;
}

getMysqlDataSource：首先启动mysql（本书使用5.4.3版本），其次登录mysql创建test数据库（“create database test;”），在进行测试前，请先下载并添加mysql-connector-java-5.1.10.jar到classpath；
{?= call FUNCTION_TEST(?)}：可以使用{?= call …}形式调用自定义函数；
params：无需使用SqlReturnResultSet提取结果集数据，而是使用SqlOutParameter来描述自定义函数返回值；
CallableStatementCreator：同上个例子含义一样；
cstmt.registerOutParameter(1, Types.INTEGER)：将OUT类型参数注册为JDBC类型Types.INTEGER，此处即返回值类型为Types.INTEGER。
outValues.get("result")：获取结果，直接返回Integer类型，比hsqldb简单多了吧。

@Test
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(0, outValues.get("outId"));
}

{call PROCEDURE_TEST(?, ?)}：定义存储过程sql；
params：定义存储过程参数；SqlInOutParameter描述INOUT类型参数、SqlOutParameter描述OUT类型参数；
CallableStatementCreator：用于创建CallableStatement，并设值及注册OUT参数类型；
outValues：通过SqlInOutParameter及SqlOutParameter参数定义的name来获取存储过程结果。

@Test
public void testNamedParameterJdbcTemplate1() {
NamedParameterJdbcTemplate namedParameterJdbcTemplate = null;
//namedParameterJdbcTemplate =
// new NamedParameterJdbcTemplate(dataSource);
namedParameterJdbcTemplate =
new NamedParameterJdbcTemplate(jdbcTemplate);
String insertSql = "insert into test(name) values(:name)";
String selectSql = "select * from test where name=:name";
String deleteSql = "delete from test where name=:name";
Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("name", "name5");
namedParameterJdbcTemplate.update(insertSql, paramMap);
final List<Integer> result = new ArrayList<Integer>();
namedParameterJdbcTemplate.query(selectSql, paramMap,
new RowCallbackHandler() {
@Override
public void processRow(ResultSet rs) throws SQLException {
result.add(rs.getInt("id"));
}
});
Assert.assertEquals(1, result.size());
SqlParameterSource paramSource = new MapSqlParameterSource(paramMap);
namedParameterJdbcTemplate.update(deleteSql, paramSource);
}

package cn.javass.spring.chapter7;
public class UserModel {
private int id;
private String myName;
//省略getter和setter
}

@Test
public void testNamedParameterJdbcTemplate2() {
NamedParameterJdbcTemplate namedParameterJdbcTemplate = null;
namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
UserModel model = new UserModel();
model.setMyName("name5");
String insertSql = "insert into test(name) values(:myName)";
SqlParameterSource paramSource = new BeanPropertySqlParameterSource(model);
namedParameterJdbcTemplate.update(insertSql, paramSource);
}

//定义UserModel的RowMapper
package cn.javass.spring.chapter7;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
public class UserRowMapper implements RowMapper<UserModel> {
@Override
public UserModel mapRow(ResultSet rs, int rowNum) throws SQLException {
UserModel model = new UserModel();
model.setId(rs.getInt("id"));
model.setMyName(rs.getString("name"));
return model;
}
}

@Test
public void testSimpleJdbcTemplate() {
//还支持DataSource和NamedParameterJdbcTemplate作为构造器参数
SimpleJdbcTemplate simpleJdbcTemplate = new SimpleJdbcTemplate(jdbcTemplate);
String insertSql = "insert into test(id, name) values(?, ?)";
simpleJdbcTemplate.update(insertSql, 10, "name5");
String selectSql = "select * from test where id=? and name=?";
List<Map<String, Object>> result = simpleJdbcTemplate.queryForList(selectSql, 10, "name5");
Assert.assertEquals(1, result.size());
RowMapper<UserModel> mapper = new UserRowMapper();
List<UserModel> result2 = simpleJdbcTemplate.query(selectSql, mapper, 10, "name5");
Assert.assertEquals(1, result2.size());
}

SimpleJdbcTemplate还提供如下方法用于获取JdbcTemplate和NamedParameterJdbcTemplate：

1）获取JdbcTemplate对象方法：JdbcOperations是JdbcTemplate的接口

JdbcOperations getJdbcOperations()

2）获取NamedParameterJdbcTemplate对象方法：NamedParameterJdbcOperations是NamedParameterJdbcTemplate的接口

NamedParameterJdbcOperations getNamedParameterJdbcOperations()

原文链接博客园不让发，是http://jinnianshilongnian.iteye.com/blog/1423897这里转过来的。

(转)JDBC模板类。,布布扣,bubuko.com

相关推荐

电脑软件

热门排行

今日推荐

核淘优购正版

版本：v1.0.20

大小：4.06MB

日期：2024-09-23
小淘省钱官方版

版本：v1.0.21

大小：37.93MB

日期：2024-09-23
淘享生活免费版

版本：v3.13.3

大小：36.88MB

日期：2024-09-23
蚂蚁好省手机版

版本：v1.0.1

大小：2.66MB

日期：2024-09-23
打折购物安卓版

版本：v1.7.1

大小：11.15MB

日期：2024-09-23
酷鸟浏览器免费版

版本：v1.0.0.1010

大小：36.42MB

日期：2024-09-23

热门手游

使命召唤16中文版

版本：v0.2.4

大小：447.61MB

日期：2024-09-24
火柴人反恐精英正版

版本：v1.03

大小：50.17MB

日期：2024-09-24
最后一战官方版

版本：v0.240723

大小：83.32MB

日期：2024-09-24
二战绝地求生正版

版本：v2.1.7

大小：65.20MB

日期：2024-09-24
三角洲行动完整版

版本：v1.201

大小：1MB

日期：2024-09-24
漂流少女安卓版

版本：v1.372

大小：83.69MB

日期：2024-09-24