spring JdbcTemplate

 

主要类:JdbcTemplate、BeanPropertyRowMapper

  spring提供了对原始Jdbc API的简单封装:

  (1)操作关系型数据库

    JdbcTemplate、HibernateTemplate

  (2)操作nosql数据库

    RedisTemplate

  (3)操作消息队列

    JmsTemplate

  需要 spring-jdbc-version.jar 和 spring-tx-version.jar(事务相关) 

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.2.6.RELEASE</version>
</dependency>

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>5.2.6.RELEASE</version>
</dependency>

 

 

例子:

package com.database.jdbctemplate;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.util.List;
import java.util.Map;

public class JdbcTemplateDemo {
    public static void main(String[] args) {
        //1.准备数据源(c3p0、druid、DriverManagerDataSource)
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
        ds.setUrl("jdbc:mysql://127.0.0.1:3306/testx?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8");
        ds.setUsername("root");
        ds.setPassword("xxx");
        //2.创建JdbcTemplate对象
        JdbcTemplate jt = new JdbcTemplate(ds);
        //3.执行操作
        List<Map<String, Object>> list = jt.queryForList("select * from person");
        for(int i=0;i<list.size() && i<1;i++) {
            Map<String,Object> map = list.get(i);
            for(String key:map.keySet()) {
                System.out.print(key+"  ");
            }
            System.out.println();
        }
        for(int i=0;i<list.size();i++) {
            Map<String,Object> map = list.get(i);
            for(String key:map.keySet()) {
                System.out.print(map.get(key)+"  ");
            }
            System.out.println();
        }
    }
}

 

  可将Driver、url、userName、password放在配置文件里,然后读入:

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/contacts?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8
username=root
password=xxx

  

try {
    Properties pro = new Properties();

    ClassLoader classLoader = JdbcUtils.class.getClassLoader();
    URL proUrl = classLoader.getResource("jdbc.properties");
    String proPath = proUrl.getPath();

    pro.load(new FileReader(proPath));
    driver = pro.getProperty("driver");
    url = pro.getProperty("url");
    user = pro.getProperty("user");
    password = pro.getProperty("password");
    
}catch(FileNotFoundException e) {
    e.printStackTrace();
}catch(IOException e) {
    e.printStackTrace();
}

 

-------------------------------------------------------------------------------------------------------------------------------------------

改:将JdbcTemplate交给IOC容器创建

  beanJdbc.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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd">

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource1"></property>
    </bean>

    <!--数据源-->
    <bean id="dataSource1" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/testx?serverTimezone=UTC&amp;useUnicode=true&amp;characterEncoding=utf8"/>    <!--注意&符号写为 &amp;  否则解析配置文件会出错-->
        <property name="username" value="root"/>
        <property name="password" value="xxx"/>
    </bean>
</beans>

  主方法:

//1.获取容器
ApplicationContext ac = new ClassPathXmlApplicationContext("beanJdbc.xml");
//2.获取jdbcTemplate
JdbcTemplate jt = ac.getBean("jdbcTemplate",JdbcTemplate.class);
//3.执行操作
//3.执行操作
List<Map<String, Object>> list = jt.queryForList("select * from person");

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

查询返回list:

(1)返回 List<Map<String,Object>>

List<Map<String, Object>> list = jt.queryForList("select * from person");

(2)定义实体类,返回实体类的List,自定义结果到实体的映射

package com.database.domain;

/*
* 数据库中表的实体类
* */

import java.io.Serializable;

public class Person implements Serializable {
    private String id;
    private String firstName;
    private String lastName;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
}

  实体类--结果映射类:

class PersonRowMapper implements RowMapper<Person> {

    @Override
    public Person mapRow(ResultSet resultSet, int i) throws SQLException {
        Person p = new Person();
        p.setId(resultSet.getString("PersonId"));
        p.setFirstName(resultSet.getString("FirstName"));
        p.setLastName(resultSet.getString("LastName"));
        return p;
    }
}

  调用:

List<Person> list2 = jt.query("select * from person where PersonId > ?", new PersonRowMapper(), 1);

 

(3)定义实体类,返回实体类的List,使用spring框架的结果--实体映射

  实体类同上;spring提供了  org.springframework.jdbc.core.BeanPropertyRowMapper

List<Person> list3 = jt.query("select * from person where PersonId > ?", new BeanPropertyRowMapper<Person>(Person.class), 1);

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

 

  

 

spring JdbcTemplate

上一篇:Django模型层之多表操作


下一篇:程序向informix数据库插入text类型的中文乱码问题