Spring JdbcTemplate

目录

spring集成datasource

Spring JdbcTemplate

---

spring集成datasource

在resources目录下创建db.properties, spring-config.xml

db.properties

#我用的是mysql的8.0.16版本,对于8系列版本有2项注意事项
# 1.jdbcUrl后面这个serverTimezone=UTC必须加
# 2. driverClass(cj必须加)：com.mysql.cj.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/mydb?serverTimezone=UTC
driverClass=com.mysql.cj.jdbc.Driver
user=root
password=root

spring-config.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">
    <context:component-scan base-package="com.spring.ljj.jdbctemplate"></context:component-scan>
    <context:property-placeholder location="classpath:db.properties"></context:property-placeholder>
    <bean id="datasource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="jdbcUrl" value="${jdbcUrl}"></property>
        <property name="driverClass" value="${driverClass}"></property>
        <property name="user" value="${user}"></property>
        <property name="password" value="${password}"></property>
    </bean>
</beans>

UserDaoImpl.java

@Service
public class UserDaoImpl {
    @Autowired
    private DataSource dataSource;

    public int save() {
        Connection connection;
        Statement statement;
        try {
            connection = this.dataSource.getConnection();
            statement = connection.createStatement();
            int result = statement.executeUpdate("insert into user values(\"cdd\",\"password\",32)");
            statement.close();
            connection.close();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}

测试类：

public class TestJdbcTemplate {
    public static void main(String[] args) throws SQLException {
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring_config.xml");
        UserDao userDao = (UserDao) context.getBean("userDaoImpl");
        int result = userDao.save();
        System.out.println("result = " + result);//返回1 数据库成功插入
    }
}

上面userDaoImpl.save需要注入数据源后获取连接，关闭连接。。。。。

可是我们只关心执行sql的语句，所以这时可以用你JdbcTemplate

Spring JdbcTemplate

db.properties保持不变

spring_config.xml加入jdbcTemplate配置

<?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">
    <context:component-scan base-package="com.spring.ljj.jdbctemplate"></context:component-scan>
    <context:property-placeholder location="classpath:db.properties"></context:property-placeholder>
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="jdbcUrl" value="${jdbcUrl}"></property>
        <property name="driverClass" value="${driverClass}"></property>
        <property name="user" value="${user}"></property>
        <property name="password" value="${password}"></property>
    </bean>
    <!-- 就加一个jdbcTemplate用的时候直接注入就行-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
</beans>

UserDaoImpl.java

@Service
public class UserDaoImpl {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public int save() {
        int result = this.jdbcTemplate.update("insert into user values(\"张三\",\"张三\",32)\n");
        return result;
    }

    public List<User> query() {
        List<User> users = this.jdbcTemplate.query("select * from user", new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                int age = resultSet.getInt("age");
                User user = new User(username, password, age);
                return user;
            }
        });
        return users;
    }
}

测试类：

public class TestJdbcTemplate {
    public static void main(String[] args) throws SQLException {
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring_config1.xml");
        UserDaoImpl userDao = (UserDaoImpl) context.getBean("userDaoImpl");
        int result = userDao.save();
        System.out.println("result = " + result);
        List<User> list = userDao.query();
        System.out.println(list);
    }
}

运行结果：

result = 1
[User{username='ljj', password='password', age=25}, User{username='cdd', password='password', age=32}, User{username='cdd', password='password', age=32}, User{username='cdd', password='password', age=32}, User{username='张三', password='张三', age=32}, User{username='张三', password='张三', age=32}, User{username='张三', password='张三', age=32}, User{username='张三', password='张三', age=32}, User{username='张三', password='张三', age=32}, User{username='张三', password='张三', age=32}, User{username='张三', password='张三', age=32}]

Process finished with exit code 0