一、JdbcTemplatey实例化对象
TestSQL类
private DataSource dataSource;
public DataSource getDataSource() {
return dataSource;
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
public void queryTable(){
//这种的JdbcTemplate是实例化对象的，不是注入的
JdbcTemplate template = new JdbcTemplate(this.dataSource);
String sql = "select * from user";
List<Map<String,Object>> dataList = template.queryForList(sql);
for(Map<String,Object> rowData :dataList){
System.out.println(rowData.get("userId")+"\t"+rowData.get("userName"));
}
}
public static void main(String[] args) {
TestSQL testSQL = (TestSQL)SpringUtil.getBean("TestSQL");
testSQL.queryTable();
}
spring配置文件
需要注入dataSource
<bean id="TestSQL" class="com.test.TestSQL">
<property name="dataSource" ref="dataSource"></property>
</bean>
二、JdbcTemplatey用Spring管理
TestSQL类
//JdbcTemplatey用spring注入
private JdbcTemplate template ;
public JdbcTemplate getTemplate() {
return template;
}
public void setTemplate(JdbcTemplate template) {
this.template = template;
}
public void queryTable() {
String sql = "select * from user";
List<Map<String, Object>> dataList = this.template.queryForList(sql);
for (Map<String, Object> rowData : dataList) {
System.out.println(rowData.get("userId") + "\t" +rowData.get("userName"));
}
}
public static void main(String[] args) {
TestSQL2 testSQL = (TestSQL2) SpringUtil.getBean("TestSQL2");
testSQL.queryTable();
}
spring配置文件
<!--注入template-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!--当没有注入dataSource的时候，将无法使用jdbcTemplate，因为还没有给数据源赋值-->
<property name="dataSource" ref="dataSource"></property>
</bean>
<bean id="TestSQL2" class="com.test.TestSQL2">
<property name="template" ref="jdbcTemplate"></property>
</bean>
三、执行DDL语句
3.1创建temp表
TestSQL类
//JdbcTemplatey用spring注入
private JdbcTemplate template ;
public JdbcTemplate getTemplate() {
return template;
}
public void setTemplate(JdbcTemplate template) {
this.template = template;
}
public void create() {
String sql = "create table temp (tempId int,tempName varchar(20))";
this.template.execute(sql);
}
public static void main(String[] args) {
TestSQL_DDL testSQL = (TestSQL_DDL) SpringUtil.getBean("TestSQL_DDL");
testSQL.create();
//执行成功后会创建temp表 字段为int tempId和varchar类型的tempName
}
spring配置文件
<!--注入template-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<bean id="TestSQL_DDL" class="com.test.TestSQL_DDL">
<property name="template" ref="jdbcTemplate"></property>
</bean>
3.2修改temp表
public void alter() {
String sql = "ALTER table temp add password varchar(10) ";
this.template.execute(sql);
}
public static void main(String[] args) {
TestSQL_DDL testSQL = (TestSQL_DDL) SpringUtil.getBean("TestSQL_DDL");
testSQL.alter();
//执行成功后会修改temp表，增加字段为varchar password
}
3.3删除temp表
public void drop() {
String sql = "drop table temp ";
this.template.execute(sql);
}
public static void main(String[] args) {
TestSQL_DDL testSQL = (TestSQL_DDL) SpringUtil.getBean("TestSQL_DDL");
testSQL.drop();
//执行成功后会删除temp表
}
四、执行DML语句
4.1insert语句—statement对象
TestSQL类
private JdbcTemplate template ;
public JdbcTemplate getTemplate() {
return template;
}
public void setTemplate(JdbcTemplate template) {
this.template = template;
}
//相当于jdbc的statement对象的操作
public void insert() {
String sql = "insert into admin (userName,password) value ('aaa','bbb')";
this.template.update(sql);
}
public static void main(String[] args) {
TestSQL_DML testSQL = (TestSQL_DML) SpringUtil.getBean("TestSQL_DML");
testSQL.insert();
//执行后会在admin表插入userName为aaa和password为bbb
}
spring配置文件
<bean id="TestSQL_DML" class="com.test.TestSQL_DML">
<property name="template" ref="jdbcTemplate"></property>
</bean>
4.2insert语句—Preparedstatemen对象（建议）
/*
相当于jdbc的Preparedstatement对象的操作,创建一个匿名内部类PreparedStatementSetter对象(建议)
*/
public void insert_2() {
String sql = "insert into admin (userName,password) value (?,?)";
this.template.update(sql, new PreparedStatementSetter() {
//匿名内部类的写法
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setString(1,"abc");
ps.setString(2,"bbb");
}
});
}
4.3insert语句—Bean对象（不建议）
/*
相当于jdbc的Preparedstatement对象的操作,创建并返回PreparedStatementSetter对象(不建议)
*/
public void insert_3() {
String sql = "insert into admin (userName,password) value (?,?)";
this.template.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1,"test1");
statement.setString(2,"testpwd");
return statement;
}
});
}
4.4使用Object数组对象（建议）
/*
相当于jdbc的Preparedstatement对象的操作,使用object数组对象(建议)
*/
public void insert_4() {
String sql = "insert into admin (userName,password) value (?,?)";
Object[] paramArray = {"aabc","dcee"};
this.template.update(sql,paramArray);
}
4.5使用Map作为参数执行insert语句
public void insertParameter() {
String sql = "insert into admin (userName,password) value (:userName,:password)";
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(this.template.getDataSource());
Map<String,Object> map = new HashMap<>();
//map中的key要和参数一致,map中的数据可以比value中指定的参数多，但不能少
map.put("userName","test2");
map.put("password","testpwd2");
template.update(sql,map);
}
4.6使用Bean作为参数执行insert语句
public void insertBean(){
String sql = "insert into admin (userName,password) value (:userName,:password)";
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(this.template.getDataSource());
/*
Bean的属性要和value中参数的属性一致，可以多不可以少
*/
User user = new User();
user.setUserName("abbvc");
user.setPassword("avvv");
user.setUserId(123);
BeanPropertySqlParameterSource propertySqlParameterSource = new BeanPropertySqlParameterSource(user);
template.update(sql,propertySqlParameterSource);
}
五、执行Query查询语句
5.1 queryForList（返回Map）
默认返回的是map，返回的是多行数据
public void queryForList() {
String sql = "select * from admin";
List<Map<String, Object>> dataList = template.queryForList(sql);
for (Map<String, Object> map : dataList) {
System.out.println(map.get("userId") + "\t" + map.get("userName"));
}
}
测试输出
1 admin
2 user
3 adb
5.2 queryForList的注意点
直接使用queryForList不能成功返回bean类型 只能返回一个字段
public void queryForListBean() {
/*错误的写法
String sql = "select * from admin";
List<User>dataList = template.queryForList(sql,User.class);
for(User user : dataList){
System.out.println(user.getUserId()+"\t"+user.getUserName()+"\t"+user.getPassword());
}
*/
String sql = "select userName from admin";
List<String> dataList = template.queryForList(sql, String.class);
for (String str : dataList) {
System.out.println(str);
}
}
错误的写法执行会报返回多个字段的错误，错误为Exception in thread "main" org.springframework.jdbc.IncorrectResultSetColumnCountException: Incorrect column count: expected 1, actual 5
正确返回数据为：
admin
user
adb
5.3 queryForMap（返回单行数据）
public void queryForMap() {
String sql = "select * from admin where userId = 2";
Map<String, Object> row = template.queryForMap(sql);
if (row != null) {
System.out.println(row.get("userId") + "\t" + row.get("userName"));
}
}
测试输出：
2 user
5.4 queryForObject（返回聚合函数）
public void queryForObject() {
String sql = "select count(1) from admin";
Long userCount = template.queryForObject(sql, long.class);
System.out.println("userCount=" + userCount);
}
测试输出：userCount=9
5.5 query返回所有数据
返回所有数据使用query函数，参数是sql语句+BeanPropertyRowMapper
public void query() {
String sql = "select * from admin";
/* 注意这个和BeanPropertySqlParameterSource对象的区别
BeanPropertyRowMapper将数据映射成一个对象
BeanPropertySqlParameterSource将Bean对象中的值赋值给命名参数
*/
BeanPropertyRowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
List<User> dataList = template.query(sql, rowMapper);
for (User user : dataList) {
System.out.println(user.getUserId() + "\t" + user.getUserName());
}
}
测试输出：
1 admin
2 user
3 adb
5.6 query_Pre（预编译）
查询语句中包过预编译对象，？形式
public void query_Pre() {
String sql = "select * from admin where userName like ? and password like ? ";
String[] Strarray = {"%a%", "%1%"};
BeanPropertyRowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
List<User> dataList = template.query(sql, rowMapper, Strarray);
for (User user : dataList) {
System.out.println(user.getUserId() + "\t" + user.getUserName());
}
}
测试输出：
1 admin
3 adb
5.7 queryNamePre（查询带命名参数）
使用：形式
public void queryNamePre() {
String sql = "select * from admin where userName like :userName and password like :password ";
NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template.getDataSource());
User user = new User();
user.setUserName("%a%");
user.setPassword("%1%");
BeanPropertySqlParameterSource propertySqlParameterSource =
new BeanPropertySqlParameterSource(user);
BeanPropertyRowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
List<User> dataList = namedTemplate.query(sql, propertySqlParameterSource, rowMapper);
for (User row : dataList) {
System.out.println(row.getUserId() + "\t" + row.getUserName());
}
}
测试输出：
1 admin
3 adb
