JDBC
实体类
package com.lyh.entity;
import java.util.Date;
public class User {
//username,password,email,birthday,以及address
private int id;
private String username;
private String password;
private String email;
private String birthday;
private String address;
public User() {
super();
// TODO Auto-generated constructor stub
}
public User(int id, String username, String password, String email, String birthday, String address) {
super();
this.id = id;
this.username = username;
this.password = password;
this.email = email;
this.birthday = birthday;
this.address = address;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getBirthday() {
return birthday;
}
public void setBirthday(String birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", password=" + password + ", email=" + email
+ ", birthday=" + birthday + ", address=" + address + "]";
}
}
工具类
package com.lyh.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
public class MyJdbcUtils {
static DataSource ds=null;
static {
try {
Properties prop=new Properties();
InputStream inputStream = MyJdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties");
prop.load(inputStream);
ds = DruidDataSourceFactory.createDataSource(prop);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection conn=null;
try {
conn=ds.getConnection();
return conn;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public static DataSource getDataSource() {
return ds;
}
public static void close(ResultSet rs, PreparedStatement stmt, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(PreparedStatement stmt, Connection conn) {
close(null, stmt, conn);
}
}
业务层接口
package com.lyh.service;
import java.util.List;
import com.lyh.entity.User;
public interface UserService {
int add(User user);
int delete(int id);
int update(int id);
//通过id查询某个用户
User findUserById(int id);
//查询所有
List<User> findAll();
//查询总记录数
int getTotalCount();
}
数据库访问层接口
package com.lyh.dao;
import java.util.List;
import com.lyh.entity.User;
public interface UserDao {
public int addUser(User user);
public int deleteUser(int id);
public int update(int id);
public User findUserByIdUser(int id);
public List<User> findAllUser() ;
public int getTotalCountUser() ;
}
数据库访问层原生JDBC实现类
package com.lyh.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.Date;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import com.alibaba.druid.util.JdbcUtils;
import com.lyh.dao.UserDao;
import com.lyh.entity.User;
import com.lyh.utils.MyJdbcUtils;
public class UserDaoImpl implements UserDao {
@Override
public int addUser(User user) {
Connection conn=null;
PreparedStatement stmt=null;
try {
conn = MyJdbcUtils.getConnection();
String sql="insert into user2(username,password,email,birthday,address) values(?,?,?,?,?)";
stmt= conn.prepareStatement(sql);
stmt.setString(1, user.getUsername());
stmt.setString(2, user.getPassword());
stmt.setString(3, user.getEmail());
stmt.setString(4, user.getBirthday());
stmt.setString(5,user.getAddress());
int count=stmt.executeUpdate();
return count;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
MyJdbcUtils.close(stmt, conn);
}
return 0;
}
@Override
public int deleteUser(int id) {
Connection conn=null;
PreparedStatement stmt=null;
try {
conn = MyJdbcUtils.getConnection();
String sql="delete from user2 where id=?";
stmt= conn.prepareStatement(sql);
stmt.setInt(1, id);
int count=stmt.executeUpdate();
return count;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
MyJdbcUtils.close(stmt, conn);
}
return 0;
}
@Override
public int update(int id) {
Connection conn=null;
PreparedStatement stmt=null;
try {
conn = MyJdbcUtils.getConnection();
String sql="update user2 set username=?,password=?,email=?,birthday=?,address=? where id=?";
stmt= conn.prepareStatement(sql);
stmt.setString(1, "王健林");
stmt.setString(2, "wjl123");
stmt.setString(3, "456@qq.com");
stmt.setString(4, "1965-08-09");
stmt.setString(5, "北京大兴");
stmt.setInt(6, id);
int count = stmt.executeUpdate();
return count;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
MyJdbcUtils.close(stmt, conn);
}
return 0;
}
@Override
public User findUserByIdUser(int id) {
Connection conn=null;
PreparedStatement stmt=null;
ResultSet rs=null;
User user=null;
try {
conn = MyJdbcUtils.getConnection();
String sql="select * from user2 where id=?";
stmt= conn.prepareStatement(sql);
stmt.setInt(1, id);
rs = stmt.executeQuery();
while(rs.next()) {
user=new User();
int id1=rs.getInt("id");
String username=rs.getString("username");
String password=rs.getString("password");
String email=rs.getString("email");
String birthday=rs.getString("birthday");
String address=rs.getString("address");
user.setId(id1);
user.setUsername(username);
user.setPassword(password);
user.setEmail(email);
user.setBirthday(birthday);
user.setAddress(address);
}
return user;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
MyJdbcUtils.close(rs, stmt, conn);
}
return null;
}
@Override
public List<User> findAllUser() {
Connection conn=null;
PreparedStatement stmt=null;
ResultSet rs=null;
List<User> list=new ArrayList<User>();
User user=null;
try {
conn = MyJdbcUtils.getConnection();
String sql="select * from user2;";
stmt= conn.prepareStatement(sql);
rs = stmt.executeQuery();
while(rs.next()) {
user=new User();
int id1=rs.getInt("id");
String username=rs.getString("username");
String password=rs.getString("password");
String email=rs.getString("email");
String birthday=rs.getString("birthday");
String address=rs.getString("address");
user.setId(id1);
user.setUsername(username);
user.setPassword(password);
user.setEmail(email);
user.setBirthday(birthday);
user.setAddress(address);
list.add(user);
}
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
MyJdbcUtils.close(rs, stmt, conn);
}
return null;
}
@Override
public int getTotalCountUser() {
Connection conn=null;
PreparedStatement stmt=null;
ResultSet rs=null;
int count=0;
try {
conn = MyJdbcUtils.getConnection();
String sql="select count(id) from user2;";
stmt= conn.prepareStatement(sql);
rs = stmt.executeQuery();
while(rs.next()) {
count=rs.getInt(1);
}
return count;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
MyJdbcUtils.close(rs, stmt, conn);
}
return 0;
}
}
数据库访问层DButils实现类
package com.lyh.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.Date;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.alibaba.druid.util.JdbcUtils;
import com.lyh.dao.UserDao;
import com.lyh.entity.User;
import com.lyh.utils.MyJdbcUtils;
public class UserDaoImpl2 implements UserDao {
@Override
public int addUser(User user) {
int count=0;
try {
QueryRunner qr=new QueryRunner(MyJdbcUtils.getDataSource());
String sql="insert into user2(username,password,email,birthday,address) values(?,?,?,?,?)";
count=qr.update(sql, user.getUsername(), user.getPassword(),user.getEmail(),user.getBirthday(),user.getAddress());
return count;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return 0;
}
@Override
public int deleteUser(int id) {
int count=0;
try {
QueryRunner qr=new QueryRunner(MyJdbcUtils.getDataSource());
String sql="delete from user2 where id=?";
count=qr.update(sql, id);
return count;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return 0;
}
@Override
public int update(int id) {
int count=0;
try {
QueryRunner qr=new QueryRunner(MyJdbcUtils.getDataSource());
String sql="update user2 set username=?,password=?,email=?,birthday=?,address=? where id=?";
count=qr.update(sql, "王健林","wjl123","456@qq.com","1965-08-09","北京大兴",id);
return count;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return 0;
}
@Override
public User findUserByIdUser(int id) {
QueryRunner qr=new QueryRunner(MyJdbcUtils.getDataSource());
User user=null;
try {
String sql="select * from user2 where id=?";
user = qr.query(sql, id, new BeanHandler<User>(User.class));
return user;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
@Override
public List<User> findAllUser() {
QueryRunner qr=new QueryRunner(MyJdbcUtils.getDataSource());
try {
String sql="select * from user2;";
List<User> list = qr.query(sql, new BeanListHandler<User>(User.class));
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
@Override
public int getTotalCountUser() {
QueryRunner qr=new QueryRunner(MyJdbcUtils.getDataSource());
try {
String sql="select count(id) from user2;";
int count = (int)qr.query(sql, new ScalarHandler<>());
return count;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return 0;
}
}
业务层原生JDBC实现类
package com.lyh.service.impl;
import java.util.List;
import com.lyh.dao.UserDao;
import com.lyh.dao.impl.UserDaoImpl;
import com.lyh.entity.User;
import com.lyh.service.UserService;
public class UserServiceImpl implements UserService {
UserDao ud=new UserDaoImpl();
@Override
public int add(User user) {
return ud.addUser(user);
}
@Override
public int delete(int id) {
return ud.deleteUser(id);
}
@Override
public int update(int id) {
return ud.update(id);
}
@Override
public User findUserById(int id) {
return ud.findUserByIdUser(id);
}
@Override
public List<User> findAll() {
return ud.findAllUser();
}
@Override
public int getTotalCount() {
return ud.getTotalCountUser();
}
}
业务层DButils实现类
package com.lyh.service.impl;
import java.util.List;
import com.lyh.dao.UserDao;
import com.lyh.dao.impl.UserDaoImpl;
import com.lyh.dao.impl.UserDaoImpl2;
import com.lyh.entity.User;
import com.lyh.service.UserService;
public class UserServiceImpl2 implements UserService {
UserDao ud=new UserDaoImpl2();
@Override
public int add(User user) {
return ud.addUser(user);
}
@Override
public int delete(int id) {
return ud.deleteUser(id);
}
@Override
public int update(int id) {
return ud.update(id);
}
@Override
public User findUserById(int id) {
return ud.findUserByIdUser(id);
}
@Override
public List<User> findAll() {
return ud.findAllUser();
}
@Override
public int getTotalCount() {
return ud.getTotalCountUser();
}
}
JUnit单元测试(原生JDBC)
package com.lyh.test;
import java.util.Date;
import java.util.List;
import org.junit.Test;
import com.lyh.entity.User;
import com.lyh.service.UserService;
import com.lyh.service.impl.UserServiceImpl;
/*
* int add(User user);
int delete(int id);
int update(int id);
//通过id查询某个用户
User findUserById(int id);
//查询所有
List<User> findAll();
//查询总记录数
int getTotalCount();
* */
public class UserTest {
@Test
public void addTest() {
User user=new User();
user.setUsername("马云");
user.setPassword("123456");
user.setEmail("123@qq.com");
user.setBirthday("1965-08-09");
user.setAddress( "杭州");
UserService us=new UserServiceImpl();
int count=us.add(user);
System.out.println(count);
}
@Test
public void deleteTest() {
UserService us=new UserServiceImpl();
int count=us.delete(4);
System.out.println(count);
}
@Test
public void updateTest() {
UserService us=new UserServiceImpl();
int count=us.update(3);
System.out.println(count);
}
@Test
public void findUserByIdTest() {
User user=null;
UserService us=new UserServiceImpl();
user=us.findUserById(3);
System.out.println(user);
}
@Test
public void findAllTest() {
List<User> list=null;
UserService us=new UserServiceImpl();
list=us.findAll();
for (User user : list) {
System.out.println(user);
}
}
@Test
public void getTotalCountTest() {//int getTotalCount();
UserService us=new UserServiceImpl();
int count = us.getTotalCount();
System.out.println(count);
}
}
JUnit单元测试(DButils)
package com.lyh.test;
import java.util.Date;
import java.util.List;
import org.junit.Test;
import com.lyh.entity.User;
import com.lyh.service.UserService;
import com.lyh.service.impl.UserServiceImpl;
import com.lyh.service.impl.UserServiceImpl2;
/*
* int add(User user);
int delete(int id);
int update(int id);
//通过id查询某个用户
User findUserById(int id);
//查询所有
List<User> findAll();
//查询总记录数
int getTotalCount();
* */
public class UserTest2 {
@Test
public void addTest() {
User user=new User();
user.setUsername("马云");
user.setPassword("123456");
user.setEmail("123@qq.com");
user.setBirthday("1965-08-09");
user.setAddress( "杭州");
UserService us=new UserServiceImpl2();
int count=us.add(user);
System.out.println(count);
}
@Test
public void deleteTest() {
UserService us=new UserServiceImpl2();
int count=us.delete(6);
System.out.println(count);
}
@Test
public void updateTest() {
UserService us=new UserServiceImpl2();
int count=us.update(3);
System.out.println(count);
}
@Test
public void findUserByIdTest() {
User user=null;
UserService us=new UserServiceImpl2();
user=us.findUserById(3);
System.out.println(user);
}
@Test
public void findAllTest() {
List<User> list=null;
UserService us=new UserServiceImpl2();
list=us.findAll();
for (User user : list) {
System.out.println(user);
}
}
@Test
public void getTotalCountTest() {//int getTotalCount();
UserService us=new UserServiceImpl();
int count = us.getTotalCount();
System.out.println(count);
}
}