Spring JDBC整合

来源：互联网发布：四维排课软件免费下载编辑：程序博客网时间：2024/06/04 22:25

1、首先导入包：

2、然后建立数据库表

CREATE DATABASE day36;

USE day36;

CREATE TABLE t_user(

id INT PRIMARY KEY,

NAME VARCHAR(20),

gender CHAR(2)

);

3、在编写实体

package star.july.entity;

public class User {

private int id;

private String name;

private String gender;

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getGender() {

return gender;

}

public void setGender(String gender) {

this.gender = gender;

}

@Override

public String toString() {

return "User [id=" + id + ", name=" + name + ", gender=" + gender + "]";

}

4、编写dao类

package star.july.dao;

import java.util.List;

import star.july.entity.User;

public interface IUserDao {

public void save(User user);

public void update(User user);

public void delete(int id);

public List<User> queryAll();

public List<User> queryPages(int curPage,int PageSize);

public User queryById(int id);

public int queryCount();

}

dao类实现接口

package star.july.dao;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

import star.july.entity.User;

public class UserDaoImpl implements IUserDao{

//用于接收jdbcTemplate对象

private JdbcTemplate jdbcTemplate;

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {

this.jdbcTemplate = jdbcTemplate;

}

//添加

public void save(User user) {

jdbcTemplate.update("insert into t_user(id,name,gender) values(?,?,?)",

user.getId(),

user.getName(),

user.getGender()

);

}

//修改

public void update(User user) {

jdbcTemplate.update("update t_user set name=?,gender=? where id=?",

user.getName(),

user.getGender(),

user.getId()

);

}

//删除

public void delete(int id) {

jdbcTemplate.update("delete from t_user where id=?",id);

}

//查询所有数据

public List<User> queryAll() {

return jdbcTemplate.query("select * from t_user",new RowMapper(

) {

public Object mapRow(ResultSet rs, int index)

throws SQLException {

User u = new User();

u.setId(rs.getInt("id"));

u.setName(rs.getString("name"));

u.setGender(rs.getString("gender"));

return u;

}

);

}

//分页查询

public List<User> queryPages(int curPage,int pageSize) {

return (List<User>)jdbcTemplate.query("select * from t_user limit ? , ?", new RowMapper(){

public Object mapRow(ResultSet rs, int arg1)

throws SQLException {

User u = new User();

u.setId(rs.getInt("id"));

u.setName(rs.getString("name"));

u.setGender(rs.getString("gender"));

return u;

}

},(curPage-1)*pageSize,pageSize);

}

public User queryById(int id) {

return jdbcTemplate.queryForObject("select * from t_user where id = ?",new RowMapper(){

public Object mapRow(ResultSet rs, int index) throws SQLException {

User u = new User();

u.setId(rs.getInt("id"));

u.setName(rs.getString("name"));

u.setGender(rs.getString("gender"));

return u;

}

},id);

}

//查询总计录数

public int queryCount() {

return jdbcTemplate.queryForObject("select count(*) from t_user", Long.class).intValue();

}

5、编写applicationContext.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>

</bean>

</bean>

</beans>

6、测试

package star.july.test;

import java.util.List;

import org.junit.Test;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import star.july.dao.IUserDao;

import star.july.dao.UserDaoImpl;

import star.july.entity.User;

public class Demo {

//添加

@Test

public void test(){

ApplicationContext ac = new ClassPathXmlApplicationContext("/applicationContext.xml");

IUserDao userDao = (IUserDao)ac.getBean("userDaoID");

User u = new User();

u.setId(3);

u.setName("徐渭熊");

u.setGender("女");

userDao.save(u);

}

//修改

@Test

public void test2(){

ApplicationContext ac = new ClassPathXmlApplicationContext("/applicationContext.xml");

IUserDao userDao = (IUserDao)ac.getBean("userDaoID");

User u = new User();

u.setId(1);

u.setName("徐奇");

u.setGender("男");

userDao.update(u);

}

//删除

@Test

public void test3(){

ApplicationContext ac = new ClassPathXmlApplicationContext("/applicationContext.xml");

IUserDao userDao = (IUserDao)ac.getBean("userDaoID");

userDao.delete(2);

}

//查询所有对象

@Test

public void test4(){

ApplicationContext ac = new ClassPathXmlApplicationContext("/applicationContext.xml");

IUserDao userDao = (IUserDao)ac.getBean("userDaoID");

List<User> list = userDao.queryAll();

for(User u : list){

System.out.println(u);

}

//根据id查找对象

@Test

public void test5(){

ApplicationContext ac = new ClassPathXmlApplicationContext("/applicationContext.xml");

IUserDao userDao = (IUserDao)ac.getBean("userDaoID");

User u = userDao.queryById(2);

System.out.println(u);

}

//查找总计录数

@Test

public void test6(){

ApplicationContext ac = new ClassPathXmlApplicationContext("/applicationContext.xml");

IUserDao userDao = (IUserDao)ac.getBean("userDaoID");

int count = userDao.queryCount();

System.out.println(count);

}

//分页查询

@Test

public void test7(){

ApplicationContext ac = new ClassPathXmlApplicationContext("/applicationContext.xml");

IUserDao userDao = (IUserDao)ac.getBean("userDaoID");

List<User> list = userDao.queryPages(2, 2);

for(User user : list ){

System.out.println(user);

}

抽取数据可的参数到properties文件，首先要配置context命名空间（红色部分）

再配置全局读取： <context:property-placeholder location="classpath:db.properties"/>

最后用表达式获取值（黑色加粗部分)

<?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:property-placeholder location="classpath:db.properties"/>

</bean>

</bean>

</bean>

</beans>

0 0