一 概念
-
Java Database Connectivity,简称JDBC
-
JDBC:sun公司定义的一套操作数据库的规范,就是接口
二 四个核心的对象
-
DriverManager:依据数据库的不同,管理JDBC驱动
-
Connection: 负责连接数据库并担任传送数据的任务
-
Statement: 由 Connection 产生、负责执行SQL语句
-
ResultSet:负责保存Statement执行后所产生的查询结果
三 使用步骤
-
注册驱动
-
获取数据库连接对象 Connection
-
定义sql
-
获取执行sql语句的对象 Statement
-
执行sql,接受返回结果
-
处理结果
-
释放资源
# 注册驱动 Class.forName("com.mysql.jdbc.Driver"); - 括号中的数据不知道怎么写的时候,可以去复制 # 获取数据库连接对象 Connection,故名思意,连接肯定需要连接的地址,用户名和密码 Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/xinzhi666", "root", "root"); - 如果java操作数据库的时候有乱码,可以在地址后面跟 ?useUnicode=true&characterEncoding=UTF-8 # 定义sql语句,就是个字符串,如果需要字符串拼接,注意单引号。 String sql = "update person set name='赵琳鹏' where id=1"; # 获取执行sql语句的对象 Statement,是由连接对象创建出来的 Statement statement = connection.createStatement(); # 执行sql,接受返回结果 - 1.查询executeQuery(SQL),会有返回值ResultSet,保存了查询的结果 - 2.增删改executeUpdate(增删改SQL),返回一个数据,表示数据库中受影响的行数 # 处理结果 - ResultSet,可以把ResultSet看成我们java里的set,迭代的时候用next()方法 - 获取每一列resultSet.getString(),括号中的参数可以是数字,从1开始,表示第几列,也可以是字符串,列名的字符串。 # 释放资源 调用close()的方法
四 sql注入
# statement执行sql语句的时候会有sql注入的风险,所以推荐使用PreparedStatemen PreparedStatement ps = con.prepareStatement(sql); sql语句中的参数可以使用?代替 statement.setInt(索引,值); 索引从1开始的 setXXX XXX代表数据类型 # 查询使用executeQuery(); 增删改使用executeUpdate();
五 工具类
public class JDBCUtils { private JDBCUtils(){} private static String url = "jdbc:mysql://localhost:3306/xinzhi666?useUnicode=true&characterEncoding=UTF-8"; private static String user="root"; private static String password="root"; private static String driver="com.mysql.jdbc.Driver"; static { try { Class.forName(driver); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public static Connection getConnection(){ Connection connection = null; try { connection = DriverManager.getConnection(url,user,password); } catch (SQLException e) { e.printStackTrace(); } return connection; } public static void release(Connection connection, Statement statement, ResultSet resultSet){ if(resultSet!=null){ try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } } if(connection!=null){ try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } if(statement!=null){ try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
六 案例
1 创建jdbc项目,并且导入资料中的jar包
2 创建JDBC工具类
package utils; import java.sql.*; /** * 工具类 ,也有人叫 BaseDao */ public class JDBCUtils { private JDBCUtils(){ } public static Connection getConnection(){ Connection connection = null; try { Class.forName("com.mysql.jdbc.Driver"); connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/xinzhi666?useUnicode=true&characterEncoding=UTF-8", "root", "root"); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException throwables) { throwables.printStackTrace(); } return connection; } public static void release(ResultSet rs, Statement statement,Connection connection){ if(rs!=null){ try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(statement!=null){ try { statement.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(connection!=null){ try { connection.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } public static void release( Statement statement,Connection connection){ if(statement!=null){ try { statement.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(connection!=null){ try { connection.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
3 加密工具
package utils; import java.math.BigInteger; import java.security.MessageDigest; import java.security.NoSuchAlgorithmException; public class MD5Utils { public static String encrypt(String password){ MessageDigest md = null; String s = null; try { md = MessageDigest.getInstance("MD5"); md.update(password.getBytes()); s = new BigInteger(1, md.digest()).toString(16); } catch (NoSuchAlgorithmException e) { e.printStackTrace(); } return s; } }
4 实体类
package model; /** * 实体类 ,一般放在model包下, entity */ public class User { private int id; private String username; private String password; private String name; private String phone; private String email; public User() { } public User(int id, String username, String password, String name, String phone, String email) { this.id = id; this.username = username; this.password = password; this.name = name; this.phone = phone; this.email = email; } 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 getName() { return name; } public void setName(String name) { this.name = name; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", password='" + password + '\'' + ", name='" + name + '\'' + ", phone='" + phone + '\'' + ", email='" + email + '\'' + '}'; } }
4 dao层代码
package dao; import model.User; public interface ILoginDao { /** * 注册 * @param user 用户的实体类 * @return 返回值是一个数字,表示数据库中受影响的行数 */ int register(User user); /** * 登录 * @param username 用户名 * @param password 密码 * @return */ User login(String username, String password); /** * 修改用户信息 * @param user * @return */ int update(User user,String pw); /** * 删除用户 * @param username * @param password * @return */ int delete(String username, String password); }
package dao.impl; import dao.ILoginDao; import model.User; import utils.JDBCUtils; import utils.MD5Utils; import java.sql.*; public class LoginDaoImpl implements ILoginDao { @Override public int register(User user) { Connection connection = null; PreparedStatement statement = null; int i = 0; try { connection = JDBCUtils.getConnection(); String sql = "insert into user () values (?,?,?,?,?,?)"; statement = connection.prepareStatement(sql); statement.setInt(1,user.getId()); statement.setString(2,user.getUsername()); statement.setString(3, MD5Utils.encrypt(user.getPassword())); statement.setString(4,user.getName()); statement.setString(5,user.getPhone()); statement.setString(6,user.getEmail()); i = statement.executeUpdate(); // 数据库中受影响的行数 } catch (SQLException throwables) { throwables.printStackTrace(); }finally { //7 释放资源 JDBCUtils.release(statement,connection); } return i; } @Override public User login(String username, String password) { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; String sql = "select id,username,password,name,phone,email from user where username=? and password=?"; try { connection = JDBCUtils.getConnection(); statement = connection.prepareStatement(sql); statement.setString(1,username); statement.setString(2,MD5Utils.encrypt(password)); resultSet = statement.executeQuery(); while (resultSet.next()){ User user = new User(); String id = resultSet.getString("id"); user.setId(Integer.parseInt(id)); user.setUsername(resultSet.getString("username")); user.setPassword(resultSet.getString("password")); user.setName(resultSet.getString("name")); user.setPhone(resultSet.getString("phone")); user.setEmail(resultSet.getString("email")); return user; } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { JDBCUtils.release(resultSet,statement,connection); } return null; } @Override public int update(User user,String pw) { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; String sql = "update user set name=?,phone=?,password=? where username=? and password=?"; try { connection = JDBCUtils.getConnection(); statement = connection.prepareStatement(sql); statement.setString(1,user.getName()); statement.setString(2,user.getPhone()); statement.setString(3,MD5Utils.encrypt(pw)); statement.setString(4,user.getUsername()); statement.setString(5,MD5Utils.encrypt(user.getPassword())); int i = statement.executeUpdate(); return i; }catch (SQLException throwables) { throwables.printStackTrace(); }finally { JDBCUtils.release(resultSet,statement,connection); } return 0; } @Override public int delete(String username, String password) { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; String sql = "delete from user where username=? and password=?"; try { connection = JDBCUtils.getConnection(); statement = connection.prepareStatement(sql); statement.setString(1,username); statement.setString(2,password); int i = statement.executeUpdate(); return i; }catch (SQLException throwables) { throwables.printStackTrace(); }finally { JDBCUtils.release(resultSet,statement,connection); } return 0; } }
5 测试
import dao.impl.LoginDaoImpl; import model.User; import java.math.BigInteger; import java.security.MessageDigest; import java.security.NoSuchAlgorithmException; import java.util.Scanner; public class Test01 { public static void main(String[] args) { LoginDaoImpl loginDao = new LoginDaoImpl(); // int num = loginDao.register(new User(6, "qinyuzhao", "123456", "老韩", "13111223344", "qinyuzhao@qq.com")); // if(num>0){ // System.out.println("注册成功"); // }else { // System.out.println("注册失败"); // } // User user = loginDao.login("qinyuzhao", "123456"); // if(user!=null){ // System.out.println("登录成功,欢迎"+user.getName()+"回来"); // }else { // System.out.println("登录失败"); // } // int update = loginDao.update(new User(6, "qinyuzhao", "123456", "秦昱照", "12315", "zhouyajun@qq.com"),"root"); // if(update>0){ // System.out.println("修改成功"); // }else { // System.out.println("修改失败"); // } // int delete = loginDao.delete("changkaixun", "zhanan"); // if(delete>0){ // System.out.println("删除成功"); // }else { // System.out.println("删除失败"); // } // Test01 test01 = new Test01(); // Scanner scanner = new Scanner(System.in); // System.out.println("请输入要加密的字符串:"); // String str = scanner.next(); // String encrypt = test01.encrypt(str); // System.out.println("加密前:"+str+",加密后的:"+encrypt); } public String encrypt(String password){ MessageDigest md = null; String s = null; try { md = MessageDigest.getInstance("MD5"); md.update(password.getBytes()); s = new BigInteger(1, md.digest()).toString(16); } catch (NoSuchAlgorithmException e) { e.printStackTrace(); } return s; } }