Spring JdbcTemplate的基本使用

--------查询操作

（1）查询单个行数Queryobject和query

Integer integer = new JdbcTemplate(dataSource).queryForObject("select count(*) from USER ", Integer.class);

）（2）查询绑定单个变量

String sql="select count(*) from USER WHERE uid=?";
Integer integer = new JdbcTemplate(dataSource).queryForObject(sql, Integer.class,"1");

（3）查询一行String(两种)

String sql="select name from USER WHERE uid=?";
1.String integer = new JdbcTemplate(dataSource).queryForObject(sql,new Object[]{1},String.class);

2.String integer = new JdbcTemplate(dataSource).queryForObject(sql,String.class,"1");

（4）查询两个条件

1.String sql="select name from USER WHERE uid=?AND password=?";
String integer = new JdbcTemplate(dataSource).queryForObject(sql,String.class,"1","1235");//这里

2.String sql="select name from USER WHERE uid=?AND password=?"; String integer = new JdbcTemplate(dataSource).queryForObject(sql,new Object[]{"1","12345"},String.class);

两种方式的底层源码

 

查询单个域对象

String sql="select * from USER WHERE uid=?";
User user=new JdbcTemplate(dataSource).queryForObject(sql, new Object[]{1}, new RowMapper<User>() {
    @Override
    public User mapRow(ResultSet resultSet, int i) throws SQLException {
       User user1=new User();
       user1.setName(resultSet.getString(1));
       user1.setName(resultSet.getString(2));
       return user1;
    }
});

查询多个域对象

上面代码也可以写成内部类方式，直接调用

public static void main(String[] args) throws Exception{
    ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
    DataSource dataSource = (DataSource) context.getBean("dataSource",DataSource.class);
    String sql="select * from USER";
    List<User> users=new JdbcTemplate(dataSource).query(sql, new Mymaperr());
    System.out.println("name"+users);

}
static class Mymaperr implements RowMapper{
    @Override
    public Object mapRow(ResultSet resultSet, int i) throws SQLException {
        User user1=new User();
        user1.setName(resultSet.getString(1));
        user1.setPasswd(resultSet.getString(2));
        return user1;
    }
}

------增加，修改删除，操作update方法

String sql="insert into USER(name,password) VALUES (?,?)";
new JdbcTemplate(dataSource).update(sql,"xu","3849");

String sql="UPDATE user SET NAME =?WHERE uid=?";
new JdbcTemplate(dataSource).update(sql,"超神的姐会走",2);

您可以使用该execute(..)方法执行任意SQL，因此该方法通常用于DDL语句。

batchUpdate方法用于执行批处理相关语句；

使用JdbcTemplate进行基本批处理操作demo如下：

spring的主配置文件ApplicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd">

    <!-- Scans within the base package of the application for @Component classes to configure as beans -->
    <context:property-placeholder location="jdbc.properties"/>
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/abcd?characterEncoding=utf-8"/>
        <property name="user" value="${jdbc.username}"/>
        <property name="password" value="1234"/>
    </bean>
</beans>