JDBC优化之路-1
1.原始JDBC使用
数据表：
项目目录：
dao层：IUserDao.java
package com.hpe.dao;
import java.util.List;
import com.hpe.pojo.User;
public interface IUserDao {
User login(User user);
List<User> selectAll();
}
dao实现层：UserDaoImpl.java
package com.hpe.dao.impl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.hpe.dao.IUserDao;
import com.hpe.pojo.User;
public class UserDaoImpl implements IUserDao {
@Override
public User login(User user) {
// 使用原生JDBC
/*
* 1.加载驱动
* 2.创建连接对象
* 3.创建执行对象
* 4.操作数据库
* 5.关闭资源
*/
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
User resUser=null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/work?useUnicode=true&characterEncoding=utf-8";
String username="root";
String password="root";
conn=DriverManager.getConnection(url, username, password);
String sql = "select * from users where name=? and pwd=?";
ps=conn.prepareStatement(sql);
ps.setString(1, user.getName());//下标从1开始
ps.setString(2, user.getPwd());
rs = ps.executeQuery();
if(rs.next()){
int id = rs.getInt("id");//也可以使用1（下标从1开始），习惯用表的列名
String name = rs.getString("name");
String pwd = rs.getString("pwd");
resUser = new User();
resUser.setId(id);
resUser.setName(name);
resUser.setPwd(pwd);
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("请正确配置驱动程序");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return resUser;
}
@Override
public List<User> selectAll() {
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
List<User>list=new ArrayList<>();
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/work?useUnicode=true&characterEncoding=utf-8";
String username="root";
String password="root";
conn=DriverManager.getConnection(url, username, password);
String sql = "select * from users ";
ps=conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
int id = rs.getInt("id");//也可以使用1（下标从1开始），习惯用表的列名
String name = rs.getString("name");
String pwd = rs.getString("pwd");
User resUser = new User();
resUser.setId(id);
resUser.setName(name);
resUser.setPwd(pwd);
list.add(resUser);
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("请正确配置驱动程序");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return list;
}
}
实体类：User.java
package com.hpe.pojo;
public class User {
private int id;
private String name;
private String pwd;
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 getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", pwd=" + pwd + "]";
}
}
测试类：MainTest.java
package com.hpe.test;
import java.util.List;
import org.junit.Test;
import com.hpe.dao.IUserDao;
import com.hpe.dao.impl.UserDaoImpl;
import com.hpe.pojo.User;
public class MainTest {
@Test
public void user_login(){
IUserDao iUserDao = new UserDaoImpl();
User user = new User();
user.setName("admin");
user.setPwd("123");
User resUser = iUserDao.login(user);
if (resUser != null){
System.out.println("登录成功");
System.out.println(resUser);
}
else{
System.out.println("登录失败");
}
}
@Test
public void selectAll(){
IUserDao iUserDao = new UserDaoImpl();
List<User> list = iUserDao.selectAll();
for (User user : list) {
System.out.println(user);
}
}
}
大量重复代码使程序很繁琐，接下来进行优化。
第一步优化:
dao实现层：UserDaoImpl.java:
package com.hpe.dao.impl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.hpe.dao.IUserDao;
import com.hpe.pojo.User;
public class UserDaoImpl implements IUserDao {
private Connection getConn() throws ClassNotFoundException, SQLException{
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/work?useUnicode=true&characterEncoding=utf-8";
String username="root";
String password="root";
Connection conn=DriverManager.getConnection(url, username, password);
return conn;
}
private void close(ResultSet rs,PreparedStatement ps,Connection conn){
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public User login(User user) {
// 使用原生JDBC
/*
* 1.加载驱动
* 2.创建连接对象
* 3.创建执行对象
* 4.操作数据库
* 5.关闭资源
*/
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
User resUser=null;
try {
conn=getConn();
String sql = "select * from users where name=? and pwd=?";
ps=conn.prepareStatement(sql);
ps.setString(1, user.getName());//下标从1开始
ps.setString(2, user.getPwd());
rs = ps.executeQuery();
if(rs.next()){
int id = rs.getInt("id");//也可以使用1（下标从1开始），习惯用表的列名
String name = rs.getString("name");
String pwd = rs.getString("pwd");
resUser = new User();
resUser.setId(id);
resUser.setName(name);
resUser.setPwd(pwd);
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("请正确配置驱动程序");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
close(rs, ps, conn);
}
return resUser;
}
@Override
public List<User> selectAll() {
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
List<User>list=new ArrayList<>();
try {
conn=getConn();
String sql = "select * from users ";
ps=conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
int id = rs.getInt("id");//也可以使用1（下标从1开始），习惯用表的列名
String name = rs.getString("name");
String pwd = rs.getString("pwd");
User resUser = new User();
resUser.setId(id);
resUser.setName(name);
resUser.setPwd(pwd);
list.add(resUser);
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("请正确配置驱动程序");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
close(rs, ps, conn);
}
return list;
}
}
第二步优化:
dao实现层：UserDaoImpl.java:
package com.hpe.dao.impl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.hpe.dao.IUserDao;
import com.hpe.pojo.User;
public class UserDaoImpl implements IUserDao {
private Connection getConn() throws ClassNotFoundException, SQLException{
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/work?useUnicode=true&characterEncoding=utf-8";
String username="root";
String password="root";
Connection conn=DriverManager.getConnection(url, username, password);
return conn;
}
private void close(ResultSet rs,PreparedStatement ps,Connection conn){
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private List<User> select(String sql,Object[] params){
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
List<User>list =new ArrayList<>();
try {
conn=getConn();
ps=conn.prepareStatement(sql);
if(params != null){
for(int i=0;i<params.length;i++){
ps.setObject(i+1, params[i]);
}
}
rs = ps.executeQuery();
while(rs.next()){
int id = rs.getInt("id");//也可以使用1（下标从1开始），习惯用表的列名
String name = rs.getString("name");
String pwd = rs.getString("pwd");
User resUser = new User();
resUser.setId(id);
resUser.setName(name);
resUser.setPwd(pwd);
list.add(resUser);
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("请正确配置驱动程序");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
close(rs, ps, conn);
}
return list;
}
private User selectObject(String sql,Object[] params){
List<User> list = select(sql, params);
User resUser = null;
if(list.size()>0){
resUser=list.get(0);
}
return resUser;
}
@Override
public User login(User user) {
String sql = "select * from users where name=? and pwd=?";
Object[] params = {user.getName(),user.getPwd()};
User resUser = selectObject(sql, params);
return resUser;
}
@Override
public List<User> selectAll() {
String sql = "select * from users ";
List<User>list=select(sql, null);
return list;
}
}
