JdbcTemplate使用的实例

1.配置环境
MySQL驱动、C3P0jar包:
c3p0-0.9.1.2.jar
mysql-connector-java-5.1.37-bin.jar

JdbcTemplate需要的jar包 :
spring-jdbc-5.0.2.RELEASE.jar
spring-core-5.0.2.RELEASE.jar
spring-core-5.0.2.RELEASE.jar
spring-tx-5.0.2.RELEASE.jar

JdbcTemplate工具类:
[Java] 纯文本查看 复制代码
?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
public class JdbcTemplateUtils {

//初始化数据源
private static DataSource ds = new ComboPooledDataSource();

// 获取JdbcTemplate对象

public static JdbcTemplate getJdbcTemplate() {

    JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
    return  jdbcTemplate;
}

public static DataSource getDataSource(){
    return ds;
}
//得到连接的方法
public static Connection getConn() throws SQLException {
    return ds.getConnection();
}

}

c3p0-config.xml:
[XML] 纯文本查看 复制代码
?
01
02
03
04
05
06
07
08
09
10
11
12
13
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>

  <property name="driverClass">com.mysql.jdbc.Driver</property>
  <property name="jdbcUrl">jdbc:mysql:///zongheanli</property>
  <property name="user">root</property>
  <property name="password">1234</property>
  <!--<property name="acquireIncrement">2</property>
  <property name="initialPoolSize">5</property>
  <property name="minPoolSize">1</property>
  <property name="maxPoolSize">5</property>-->

</default-config>
</c3p0-config>

JdbcTemplate建表例子:
[Java] 纯文本查看 复制代码
?
1
2
3
4
5
String sql = "create table student1(id int primary key auto_increment,name varchar(20) not null,birthday date)";

 //1. 创建JdbcTemplate对象,传入C3P0连接池,模板对象自己获得连接并且自己释放资源
 JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
 //2. 创建表,执行DDL语句
 jdbcTemplate.execute(sql);

JdbcTemplate插入例子:
[Java] 纯文本查看 复制代码
?
01
02
03
04
05
06
07
08
09
10
11
12
13
JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDruidDataSource());
jdbcTemplate.update("insert into student1 values (null, ?,?)", "孙悟空","1999-12-10");
jdbcTemplate.update("insert into student1 values (null, ?,?)", "孙悟天","1999-12-10");
jdbcTemplate.update("insert into student1 values (null, ?,?)", "孙悟饭","1999-12-10");
jdbcTemplate.update("insert into student1 values (null, ?,?)", "龟仙人","1999-12-10");
jdbcTemplate.update("insert into student1 values (null, ?,?)", "牛魔王","1999-12-10");
jdbcTemplate.update("insert into student1 values (null, ?,?)", "18号","1997-12-10");

int row = jdbcTemplate.update("update student1 set name=?, birthday=? where id=?", "苦林", "1994-12-10", 2);
System.out.println("更新了" + row + "行记录");

int row = jdbcTemplate.update("delete from student1 where id=?", 6);
System.out.println("删除" + row + "行记录");

JdbcTemplate queryForObject查询例子:
[Java] 纯文本查看 复制代码
?
1
2
3
4
5
6
7
8
//创建模板类的对象

    JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
    //书写sql语句
    String sql = "select count(*) from student1";
    //执行方法,传入参数开始查询
    int count = jdbcTemplate.queryForObject(sql, int.class);
    //输出查询结果
    System.out.println(count);

JdbcTemplate queryForMap查询例子:
[Java] 纯文本查看 复制代码
?
1
2
3
JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());

   Map<String, Object> map = jdbcTemplate.queryForMap("select * from student where id=?", 2);
   System.out.println(map);

JdbcTemplate queryForList查询例子:
[Java] 纯文本查看 复制代码
?
1
2
3
4
5
JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());

   List<Map<String, Object>> mapList = jdbcTemplate.queryForList("select * from student1");
   for (Map<String,Object> map: mapList ) {
       System.out.println(map);
   }

JdbcTemplate query查询的无参数例子:
[Java] 纯文本查看 复制代码
?
1
2
3
4
5
JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());

    List<Student> students = jdbcTemplate.query("select * from student", new BeanPropertyRowMapper<>(Student.class));
    for (Student student : students) {
        System.out.println(student);
    }

JdbcTemplate query查询的数组参数例子:
[Java] 纯文本查看 复制代码
?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
String name="lisi";

   int password=23;
   String sql = "select * from student where name = ? and age = ?";
   JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
   Object[] args = {name,password};
   List<Student> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Student.class), args);
   System.out.println(list)

   String name="lisi";
   int password=23;
   String sql = "select * from student where name = ? and age = ?";
   JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
   Object[] args = {name,password};
   List<Student> list = jdbcTemplate.query(sql,args, new BeanPropertyRowMapper<>(Student.class));
   System.out.println(list);

JdbcTemplate query查询的可变参数例子:
[Java] 纯文本查看 复制代码
?
1
2
3
4
5
6
7
String name="lisi";

   int password=23;
   String sql = "select * from student where name = ? and age = ?";
   JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
   Object[] args = {name,password};
   List<Student> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Student.class),name,password);
   System.out.println(list);

JdbcTemplate query查询的new PreparedStatementCreator()例子:
[Java] 纯文本查看 复制代码
?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
String name="lisi";

   int password=23;

   JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
   List<Student> list = jdbcTemplate.query(new PreparedStatementCreator() {
       @Override
       public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
           String sql = "select * from student where name = ? and age = ?";
           PreparedStatement prepar = conn.prepareStatement(sql);
           prepar.setString(1, "lisi");
           prepar.setInt(2, 23);
           return prepar;
       }
   }, new BeanPropertyRowMapper<>(Student.class));
   System.out.println(list);

JdbcTemplate query查询的new PreparedStatementSetter()例子:
[Java] 纯文本查看 复制代码
?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
String name="lisi";

    int password=23;

    JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
    String sql = "select * from student where name = ? and age = ?";
    List<Student> list = jdbcTemplate.query(sql, new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement prepare) throws SQLException {
            prepare.setString(1, "lisi");
            prepare.setInt(2, 23);
        }
    }, new BeanPropertyRowMapper<>(Student.class));
    System.out.println(list);
}

评论

暂无评论