JavaEE04-Spring的JdbcTemplate

目的

减少用JDBC编写访问数据库的程序中的重复代码.

连接数据库, 创建JdbcTemplate

以连接JDK自带的Java DB(Apache Derby)为例

package conf;

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

public class AppConfig {

    public JdbcTemplate jdbcTemplate() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("org.apache.derby.jdbc.ClientDriver");
        dataSource.setUrl("jdbc:derby://localhost:1527/sample");
        dataSource.setUsername("app");
        dataSource.setPassword("app");
        return new JdbcTemplate(dataSource);
    }
}

访问数据库的DAO

访问数据库的DAO编写示例

package dao;

import java.util.List;
import model.User;
import org.springframework.jdbc.core.JdbcTemplate;

public class UserDao {

    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public List<User> getAll() {
        List<User> users = this.jdbcTemplate.query("select * from person", (rs, rownum) -> {
            return new User(rs.getString("userID"), rs.getInt("age"));
        });
        return users;
    }

    public void createTable() {
        String sql = "CREATE TABLE person\n"
                + "(\n"
                + "userID varchar(255),\n"
                + "age int,\n"
                + "CONSTRAINT pk_userID PRIMARY KEY (userID)"
                + ")";
        this.jdbcTemplate.execute(sql);
    }
    
    public void add(User user){
        String sql = "INSERT INTO APP.PERSON (USERID, AGE) VALUES (?, ?)";
        this.jdbcTemplate.update(sql, user.getUserID(), user.getAge());
    }
     
    public static void main(String[] args) {
        JdbcTemplate jdbcTemplate1 = new conf.AppConfig().jdbcTemplate();
        UserDao userDao = new UserDao();
        userDao.setJdbcTemplate(jdbcTemplate1);
        //userDao.createTable();
        userDao.add(new User("用户03",27));
        List<User> all = userDao.getAll();
        all.stream().forEach(user -> System.out.println(user.getUserID() + " : " + user.getAge()));
    }
}

模型

package model;
public class User {
    private String userID;
    private int age;

    public User() {
    }

    public User(String userID, int age) {
        this.userID = userID;
        this.age = age;
    }

    public String getUserID() {
        return userID;
    }

    public void setUserID(String userID) {
        this.userID = userID;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "User{" + "userID=" + userID + ", age=" + age + '}';
    }
}

数据库

app.person表的数据如下
Person表内容

运行结果

user00 : 19
用户01 : 22
用户03 : 27

结果说明, 增加一个用户, userDao.add(new User(“用户03”,27));
然后用 List all = userDao.getAll(); 查询出增加后的全部用户.

package com.org.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.PreparedStatementCreator; import org.springframework.jdbc.core.RowCallbackHandler; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.stereotype.Repository; import com.org.JdbcTempBaseDao; import com.org.dao.IUserDao; import com.org.model.User; @Repository @SuppressWarnings("all") public class UserDaoImpl extends JdbcTempBaseDao implements IUserDao { @Override public List<User> getUserList() { String sql="select * from user "; final List<User> list= new ArrayList<User>(); jdbcTemplate.query(sql, new RowCallbackHandler(){ @Override public void processRow(ResultSet rs) throws SQLException { User u=new User(); u.setId(rs.getInt("id")); u.setUsername(rs.getString("username")); u.setPassword(rs.getString("password")); u.setCreateDate(rs.getString("createDate")); u.setModifyDate(rs.getString("modifyDate")); u.setType(rs.getString("type")); list.add(u); } }); return list; } @Override public List<User> getUserLists(Map<String, Object> map) { return null; } @Override public Integer getUserCount(Map<String, Object> map) { String sql = "select count(1) from User where id=? "; return getJdbcTemplate().queryForObject(sql, Integer.class,map); } @Override public User getUserById(Integer primaryKeyId) { String sql = "select id,username, password, createDate, modifyDate,type from User where id=?"; List<User> userList = getJdbcTemplate().query(sql, new BeanPropertyRowMapper(User.class), primaryKeyId); if(userList.size() == 0) { return null; } return userList.get(0); } @Override public void delUserById(Integer primaryKeyId) { String sql = "delete from user where id=?"; getJdbcTemplate().update(sql, primaryKeyId); } @Override public User addUser(final User entity) { final String sql = "insert into User(username, password, createDate, modifyDate,type) values(?,?,?,?,?)"; GeneratedKeyHolder keyHolder = new GeneratedKeyHolder(); getJdbcTemplate().update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement psst = connection.prepareStatement(sql, new String[]{"id"}); psst.setString(1, entity.getUsername()); psst.setString(2, entity.getPassword()); psst.setString(3, entity.getCreateDate()); psst.setString(4, entity.getModifyDate()); psst.setString(5, entity.getType()); return psst; } }, keyHolder); entity.setId(keyHolder.getKey().intValue()); return entity; } @Override public void editUser(User entity) { String sql="update user set username=?,password=?"; jdbcTemplate.update(sql, User.class,entity); } }
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值