本文主要演示如何封装数据库的工具类,通过MySQL中user表来进行。
一、先创建一个JAVA项目,要连接数据库必须导包,在项目中创建lib文件夹,把mysql-connector-java-5.1.22-bin.jar文件放入,右键-->build path,导包完成。在src目录下直接创建一个配置文件DBConfig.properties。
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/dbbj?useEncoding=true&characterEncoding=utf-8
username=root
password=123456
二、src目录下创建工具类包com.qf.utils,然后创建DBUtils.java文件。
package com.qf.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
public class DBUtils {
private static String driverClass;
private static String url;
private static String username;
private static String password;
static {
//获取配置文件内容,参数是配置文件的完整路径+文件名,src目录下,所以不用写路径
ResourceBundle rb = ResourceBundle.getBundle("DBConfig");
//调用getString方法通过key获取values
driverClass=rb.getString("driverClass");
url=rb.getString("url");
username=rb.getString("username");
password=rb.getString("password");
//加载驱动
try {
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//建立连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//关闭资源
public static void closeAll(ResultSet rs,Statement stmt,Connection conn) {
try {
if(rs!=null) {
rs.close();
}
if(stmt!=null) {
stmt.close();
}
if(conn!=null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
三、在数据库中创建一个user表,包含id、username、password三列,加入一条数据(1,admin,123456),只为后续的演示要用。返回JAVA项目 ,src下创建com.qf.jdbc包,创建Demo01.java文件,主方法运行文件。
package com.qf.jdbc;
import java.util.Scanner;
import com.qf.bean.User;
import com.qf.db.DoLogin2;
public class Demo01 {
public static void main(String[] args) {
// 键盘录入用户名 和 密码
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名");
String username = sc.nextLine();
System.out.println("请输入密码");
String password = sc.nextLine();
//此时去创建User类(第四步),然后才能创建实体对象
//封装对象
User user = new User();
user.setUsername(username);
user.setPassword(password);
//创建分层(第五步),具体要做的动作由该类实现
//调用具体的业务处理层
DoLogin login = new DoLogin();
User user_back = login.findUser(user);
if(user_back == null){
System.out.println("登录失败");
}else{
System.out.println("登录成功 用户id 为"+user_back.getId());
}
sc.close();
}
}
四、创建User类,在src下创建com.qf.bean包,创建User.java文件。
package com.qf.bean;
public class User {
private int id;
private String username;
private String password;
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;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", password=" + password + "]";
}
}
五、创建DoLogin
package com.qf.db;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.qf.bean.User;
import com.qf.utils.DBUtils;
//获取 Demo01 中的用户名 密码 连接数据库 进行查询
public class DoLogin {
public User findUser(User user){
User user_back = null;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = DBUtils.getConnection();
stmt = conn.createStatement();
String sql = "select * from user where username='"+user.getUsername()+"' and password='"+user.getPassword()+"'";
rs = stmt.executeQuery(sql);
if(rs.next()){
user_back = new User();
user_back.setId(rs.getInt(1));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtils.closeAll(rs, stmt, conn);
}
return user_back;
}
}
上述演示中,sql语句有安全隐患,当键盘输入为“
a' or '1'='1 ”这个时,也会提示登录成功,因为此时的sql语句为
select * from user where username='admin' and password='ddd' or '1'='1';
这个sql语句where后条件恒成立,所以一定会返回数据库的数据,这显然是不允许的。
为此,需要使用另一个可以执行sql语句的类,PreparedStatement预编译,重写一个DoLogin类,然后在Demo01中改为调用该方法,就可以避免上述错误。
package com.qf.db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.qf.bean.User;
import com.qf.utils.DBUtils;
public class DoLogin2 {
public User findUser(User user){
User user_back = null;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = DBUtils.getConnection();
//创建PreparedStatement对象 封装sql 语句
pstmt = conn.prepareStatement("select * from user where username=? and password=?");
//给问号 赋值 参数索引从1开始
pstmt.setString(1, user.getUsername());
pstmt.setString(2, user.getPassword());
//执行并返回结果集,调用方法无参数
rs = pstmt.executeQuery();
if(rs.next()){
user_back = new User();
user_back.setId(rs.getInt(1));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtils.closeAll(rs, pstmt, conn);
}
return user_back;
}
}
附上使用PreparedStatement类,对数据库的增删改操作,创建Demo02文件。
package com.qf.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.junit.Test;
import com.qf.utils.DBUtils;
//使用PreparedStatement 完成 增删改操作
public class Demo_02 {
//增加
@Test
public void testInsert(){
try {
Connection conn = DBUtils.getConnection();
String sql = "insert into user(id,username,password) values(?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 2);
pstmt.setString(2, "张三");
pstmt.setString(3, "321");
int line = pstmt.executeUpdate();
if(line>0){
System.out.println("success");
}else{
System.out.println("error");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//修改
@Test
public void testUpdate(){
try {
Connection conn = DBUtils.getConnection();
String sql = "update user set username=?,password=? where id=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "abc");
pstmt.setString(2, "654");
pstmt.setInt(3, 2);
int line = pstmt.executeUpdate();
if(line>0){
System.out.println("success");
}else{
System.out.println("error");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//删除
@Test
public void testDelete(){
try {
Connection conn = DBUtils.getConnection();
String sql = "delete from user where id=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 2);
int line = pstmt.executeUpdate();
if(line>0){
System.out.println("success");
}else{
System.out.println("error");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}