Spring-JDBC

前序

在实验普通的JDBC操作数据库时，需要打开和关闭数据库连接等语句。而Spring JDBC框架负责所有的底层细节，我们只需要定义连接数据库的参数，指定执行的SQL语句即可。

示例

导入相关依赖

<properties>
    <java.version>1.8</java.version>
    <spring-framework.version>5.1.10.RELEASE</spring-framework.version>
 </properties>
<dependencies>
    <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-jdbc</artifactId>
          <version>${spring-framework.version}</version>
    </dependency>
    <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>8.0.17</version>
    </dependency>

</dependencies>

配置文件：

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


    <bean id="dataSource"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource">
            <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
            <property name="url" value="jdbc:mysql://localhost/stuman?serverTimezone=GMT%2B8"/>
            <property name="username" value="root"/>
            <property name="password" value="123"/>
    </bean>

    <bean id="UserDaoImpl" class="com.on1.dao.impl.UserDaoImpl">
        <property name="dataSource" ref="dataSource"/>
    </bean>

</beans>

数据表user及其对应类:

CREATE TABLE `user`  (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `uname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `uPassword` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`uid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

public class User {
    private int uid;
    private String uname;
    private String password;
    //省略 get/set toString方法
}

UserDao接口及其实现类

public interface UserDao {
    void addUser(String uname, String uPassword);
    void deleteUser(int uid);
    void updateUser(int uid, String uname, String uPassword);
    User getUser(int id);
    List<User> listUser();
}

public class UserDaoImpl implements UserDao {
    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public void addUser(String uname, String uPassword) {
        String sql = "insert into user(uname, uPassword) values (?, ?)";
        jdbcTemplate.update(sql, uname, uPassword);
        System.out.println("成功添加用户 " + uname);
    }

    public void deleteUser(int uid) {
        String SQL = "delete from user where uid = ?";
        jdbcTemplate.update(SQL, uid);
        System.out.println("成功删除用户");
    }

    public void updateUser(int uid, String uname, String uPassword) {
        String SQL = "update user set uname = ?,  uPassword = ? where uid = ?";
        jdbcTemplate.update(SQL, uname, uPassword, uid);
        System.out.println("成功修改用户信息");
    }

    public User getUser(int id) {
        String SQL = "select * from user where uid = ?";
        User user = jdbcTemplate.queryForObject(SQL, new Object[]{id}, new UserMapper());
        return user;
    }

    public List<User> listUser() {
        String SQL = "select * from user";
        List<User> userList = jdbcTemplate.query(SQL, new UserMapper());
        return userList;
    }
}

public class UserMapper implements RowMapper<User> {
    public User mapRow(ResultSet resultSet, int rowNum) throws SQLException {
        User user = new User();
        user.setUid(resultSet.getInt("uid"));
        user.setUname(resultSet.getString("uname"));
        user.setPassword(resultSet.getString("uPassword"));
        return user;
    }
}

测试一下：

    public static void main(String[] args) {
        ApplicationContext ac = new ClassPathXmlApplicationContext("JDBC-config.xml");
        UserDao userDao = (UserDao)ac.getBean("UserDaoImpl");

        userDao.addUser("C2y", "ida");
        userDao.addUser("37dWIS", "guagua");
        System.out.println("--------------------------");
        List<User> userList = userDao.listUser();
        for(User user : userList) {
            System.out.println(user);
        }
        System.out.println("--------------------------");
        userDao.updateUser(3, "on1", "biubiu");
        userDao.updateUser(1, "NoName", "gugugug");
        System.out.println("--------------------------");
        User user = userDao.getUser(1);
        System.out.println(user);
    }