Spring学习笔记之Spring JDBCTemplate

最新推荐文章于 2021-10-11 21:42:13 发布

WangLi77777

最新推荐文章于 2021-10-11 21:42:13 发布

阅读量161

点赞数

分类专栏： Java Spring 文章标签： Spring的JDBCTemplate

本文链接：https://blog.csdn.net/a1151723225/article/details/79888927

版权

Java 同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

Spring

4 篇文章 0 订阅

订阅专栏

一、开发JDBCTemplate入门

Maven项目中pom文件配置：

<properties>
    <spring-version>4.3.13.RELEASE</spring-version>
  </properties>
  <dependencies>
    <!--normal need   start-->
    <!-- https://mvnrepository.com/artifact/commons-logging/commons-logging -->
    <dependency>
      <groupId>commons-logging</groupId>
      <artifactId>commons-logging</artifactId>
      <version>1.1.3</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-core -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>${spring-version}</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-beans -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-beans</artifactId>
      <version>${spring-version}</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>${spring-version}</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-expression -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-expression</artifactId>
      <version>${spring-version}</version>
    </dependency>
    <!--normal need   end-->
    <!--Spring JdbcTemplate need start-->
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>${spring-version}</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>${spring-version}</version>
    </dependency>
    <!--Spring JdbcTemplate need end-->
    <!--mysql driver    start-->
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>6.0.6</version>
    </dependency>
    <!--mysql driver    end-->
    <!--Spring test need    start-->
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-test -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-test</artifactId>
      <version>${spring-version}</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/junit/junit -->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
    </dependency>
    <!--Spring test need    end-->
  </dependencies>

自定义的MyJdbcTemplate类：（实际上并不使用这个，而是在配置文件中配置即可）

public class MyJdbcTemplate {
    private static JdbcTemplate jdbcTemplate = null;

    private MyJdbcTemplate() { }

    /*获取DriverManagerDataSource*/
    public static JdbcTemplate getInstance() throws IOException {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        //配置文件的相对路径
        String filePath = "db.properties";
        //获取文件的输入流
        InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(filePath);
        //获取配置文件对象
        Properties prop = new Properties();
        //加载文件的输入流
        prop.load(is);
        //获取配置文件中的信息
        String url = prop.getProperty("url");
        String driver = prop.getProperty("driver");
        String userName = prop.getProperty("userName");
        String password = prop.getProperty("password");
        //设置参数
        dataSource.setDriverClassName(driver);
        dataSource.setUrl(url);
        dataSource.setUsername(userName);
        dataSource.setPassword(password);
        //根据DriverManagerDataSource获取JdbcTemplate
        jdbcTemplate = new JdbcTemplate(dataSource);
        return jdbcTemplate;
    }
}

将db.properties数据库配置文件加载到applicationContext.xml中：

<!--配置db.properties数据库配置信息文件-->
    <!--方式一：Spring默认的-->
    <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="location" value="classpath:db.properties"/>
    </bean>
    <!--方式二（推荐使用）-->
    <context:property-placeholder location="classpath:db.properties"/>

Spring默认连接池：

<!--Spring默认的连接池-->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${driver}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${userName}"/>
        <property name="password" value="${password}"/>
    </bean>
<!--定义JdbcTemplate-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

这里注意：

JDBC连接mysql-connector-java 5时DriverClass是com.mysql.jdbc.Driver

JDBC连接mysql-connector-java 6时DriverClass时com.mysql.cj.jdbc.Driver，且需要指定时区：

<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/spring_jdbc?useUnicode=true&useJDBCCompliantTimezoneShift=true
&useLegacyDatetimeCode=false&serverTimezone=UTC"/>

测试类：

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class SpringJdbcTest {
    @Autowired
    @Qualifier("jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Test
    public void test2(){
        jdbcTemplate.execute("CREATE TABLE user(id int PRIMARY KEY auto_increment,name VARCHAR(20))");
    }
    @Test
    public void test() throws IOException {
        JdbcTemplate template = MyJdbcTemplate.getInstance();
        template.execute("CREATE TABLE user(id int PRIMARY KEY auto_increment,name VARCHAR(20))");
    }
}

一般不使用Spring默认的连接池，而是其他的例如DBCP，C3P0，还有阿里的druid。

DBCP：

先导入依赖包：

    <dependency>
      <groupId>commons-dbcp</groupId>
      <artifactId>commons-dbcp</artifactId>
      <version>1.4</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/commons-pool/commons-pool -->
    <dependency>
      <groupId>commons-pool</groupId>
      <artifactId>commons-pool</artifactId>
      <version>1.6</version>
    </dependency>

配置文件：

<!--使用dbcp的连接池-->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="${driver}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${userName}"/>
        <property name="password" value="${password}"/>
    </bean>

C3P0：

导入依赖包：

    <dependency>
      <groupId>com.mchange</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.5.2</version>
    </dependency>

配置文件：

<!--使用c3p0的连接池-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.cj.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/spring_jdbc?useUnicode=true&useJDBCCompliantTimezoneShift=true
&useLegacyDatetimeCode=false&serverTimezone=UTC"/>
        <property name="user" value="root"/>
        <property name="password" value="root"/>
    </bean>

Druid：

导入依赖包：

<dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.1.6</version>
    </dependency>

配置文件：

<!-- 阿里 druid 数据库连接池 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${driver}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${userName}"/>
        <property name="password" value="${password}"/>
    </bean>

使用JDBC模板：完成增删改的操作

注意在配置文件中配置好Bean，要注入jdbcTemplate

<bean id="userDao" class="com.wl.jdbc_demo1.UserDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>

UserDaoImpl 记得继承JdbcDaoSupport 类：

public class UserDaoImpl extends JdbcDaoSupport implements UserDao {

    /*增*/
    public void add(User user) {
        String sql = "insert into user values(null,?)";
        this.getJdbcTemplate().update(sql,user.getName());
    }

    /*删*/
    public void delete(User user) {
        String sql = "delete from user where id =?";
        this.getJdbcTemplate().update(sql,user.getId());
    }
    /*改*/
    public void update(User user) {
        String sql = "update user set name = ? where id = ?";
        this.getJdbcTemplate().update(sql,user.getName(),user.getId());
    }

使用JDBC模板：完成查询操作

查询更复杂一点，分为简单查询和复杂查询：
1.简单查询：
select count(*) from user     ---queryForInt(String sql)
    select name from user where id = ? ---queryForObject(String sql,Class clazz,Object... args)
2.复杂查询：
select * from user where id= ? ---queryForObject(String sql,RowMapper<T> rowObject,Object... args)
select * from user      ---query(String sql,RowMapper<T> rowObject,Object... args)

    public int findCount(){
        String sql = "select count(*) from user";
        Integer count = this.getJdbcTemplate().queryForObject(sql, Integer.class);
        return count;
    }

    public String findNameById(int id){
        String sql = "select name from user where id = ?";
        String name = this.getJdbcTemplate().queryForObject(sql, String.class,id);
        return name;
    }

    public User findAllById(int id){
        String sql = "select * from user where id = ?";
        User user = this.getJdbcTemplate().queryForObject(sql, new MyRowMapper(), id);
        return user;
    }

    public List<User> findAll() {
        String sql = "select *from user";
        List<User> userList = this.getJdbcTemplate().query(sql, new MyRowMapper());
        return userList;
    }

    class MyRowMapper implements RowMapper<User> {
        public User mapRow(ResultSet resultSet, int i) throws SQLException {
            User user = new User();
            user.setId(resultSet.getInt("id"));
            user.setName(resultSet.getString("name"));
            return user;
        }

测试类：

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class JdbcTest2 {
    @Autowired
    @Qualifier("userDao")
    private UserDao userDao;
    @Resource(name = "user")
    private User user;

    //简单查询
    @Test
    public void findCountTest(){
        int count = userDao.findCount();
        System.out.println(count);
    }

    @Test
    public void findNameByIdTest(){
        String name = userDao.findNameById(2);
        System.out.println(name);
    }

    //复杂查询
    @Test
    public void findAllByIdTest(){
        user = userDao.findAllById(3);
        System.out.println(user);
    }
    @Test
    public void findAllTest(){
        List<User> userList = userDao.findAll();
        for (User user:userList
             ) {
            System.out.println(user);
        }
    }
    
    @Test
    //增
    public void add(){
        user.setName("wangLi777");
        userDao.add(user);
    }
    @Test
    //改
    public void update(){
        user.setId(1);
        user.setName("wangLi777");
        userDao.update(user);
    }
    @Test
    //删
    public void delete(){
        user.setId(1);
        userDao.delete(user);
    }
}

WangLi77777

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Spring学习笔记之Spring JDBCTemplate

一、开发JDBCTemplate入门Maven项目中pom文件配置：&lt;properties&gt; &lt;spring-version&gt;4.3.13.RELEASE&lt;/spring-version&gt; &lt;/properties&gt; &lt;dependencies&gt; &lt;!--normal need start--&gt; ...
复制链接

扫一扫