实现JDBC增删改查
DBC(Java Data Base Connectivity,java数据库连接)是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成。
JDBC为开发人员提供了一个标准的API,据此可以构建更高级的工具和接口,使数据库开发人员能够使用java API编写数据库应用程序,并且可跨平台运行,并且不受数据库供应商的限制。
JDBC为我们提供了java连接数据库的驱动。而这个驱动也是由Java开发出来的,我们只需要将这个驱动放进项目中,通过这个驱动,我们就可以用Java连接数据库,进行数据库的管理操作。
1.准备工作
1.1创建java项目
1.2导入jar包
使用JDBC操作数据库,需要导入JDBC的驱动包:mysql-connector-java-5.0.8-bin.jar
在项目下面创建一个文件夹:lib,将驱动包复制到lib下面,并将jar包加载到项目中,如下图所示:
l 新建lib文件夹:
l 将jar包复制到lib文件夹下面:
l 选中jar包,单击右键,在弹出的窗口中选择“Add to Build Path” ,将jar包加载到项目中
2JDBC连接数据库
2.1 加载JDBC驱动
通过java.lang.Class类的静态方法forName(driver)实现
2.2 提供JDBC连接的参数
连接数据库时,需要下面几个参数:
//这个可以解决中文乱码,在最后面可以设置编码,这里设的是utf-8
url=jdbc:mysql://localhost:3306/Java7?useSSL=true&characterEncoding=utf-8
username=root
password=123
其中,username和password是连接数据库的用户名和密码,一般默认的用户名是root,密码是安装MySQL时的密码。url是连接数据库的地址。
当我们访问的是本机的数据库的时候,url的值也可以为“jdbc:mysql:///database_name”
//mysql驱动包名
private static final String DRIVER_NAME = "com.mysql.jdbc.Driver";
//数据库连接地址
private static final String URL = "jjdbc:mysql://localhost:3306/Java7?useSSL=true&characterEncoding=utf-8";
//用户名
private static final String USER_NAME = "root";
//密码
private static final String PASSWORD = "*****";
2.3 创建数据库的连接
通过DriverManager类创建数据库连接对象Connection。DriverManager类作用于Java程序和JDBC驱动程序之间,用于检查所加载的驱动程序是否可以建立连接,然后通过它的getConnection方法,根据数据库的URL、用户名和密码,创建一个JDBC Connection 对象。
try {
//试图建立到给定数据库 URL 的连接
Connection con = DriverManager.getConnection(url,username, password);
} catch (SQLExceptione) {
System.out.println("数据库连接失败!");
e.printStackTrace();
}
3.增删改查操作
DBUtil工具类
package com.day1;
import java.sql.*;
public class DBUtil {
private static String driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/Java7?useSSL=true&characterEncoding=utf-8";
private static String user = "root";
private static String password = "123";
//1.获取驱动
static{
try {
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
//2.创建连接
public static Connection get_Conn() throws SQLException{
Connection conn = DriverManager.getConnection(url,user,password);
//System.out.println("数据库连接成功"+conn);
return conn;
}
//3.关闭连接
public static void get_CloseConn(ResultSet rs, PreparedStatement pstm,Connection conn) throws SQLException {
if(rs != null){
rs.close();
}
if(pstm != null){
pstm.close();
}
if(conn != null){
conn.close();
}
}
//称作白盒测试 单元测试
public static void main(String args[]){
try {
get_Conn();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Func增删改查具体实现类
package com.day1;
import java.sql.*;
import java.util.Scanner;
public class Func {
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
public void addUser(User user) {
try {
conn = DBUtil.get_Conn();
pstm = conn.prepareStatement("insert into tb_user(username,password) values(?,?)");
pstm.setString(1, user.getUsername());
pstm.setString(2, user.getPassword());
int i = pstm.executeUpdate();
if (i > 0) {
System.out.println("增加成功");
} else {
System.out.println("增加失败");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
DBUtil.get_CloseConn(rs, pstm, conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void DeleteUser(User user){
try {
conn = DBUtil.get_Conn();
pstm = conn.prepareStatement("delete from tb_user where username=? and password=?");
pstm.setString(1,user.getUsername());
pstm.setString(2,user.getPassword());
int i = pstm.executeUpdate();
if(i>0){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
DBUtil.get_CloseConn(rs,pstm,conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void UpdateUser(User user){
try {
conn = DBUtil.get_Conn();
pstm = conn.prepareStatement("update tb_user set username=?,password=? where username=? and password=?");
Scanner sc = new Scanner(System.in);
System.out.print("请输入新的用户名:");
String nUsername = sc.next();
System.out.print("请输入新的密码:");
String nPassword = sc.next();
pstm.setString(1,nUsername);
pstm.setString(2,nPassword);
pstm.setString(3,user.getUsername());
pstm.setString(4,user.getPassword());
int i = pstm.executeUpdate();
if(i>0){
System.out.println("修改成功");
}else{
System.out.println("修改失败");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
DBUtil.get_CloseConn(rs,pstm,conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void SearchUser(User user){
try {
conn = DBUtil.get_Conn();
pstm = conn.prepareStatement("select * from tb_user where username=?");
pstm.setString(1,user.getUsername());
rs = pstm.executeQuery();
if(rs.next()){
System.out.println("查询成功");
System.out.println("用户ID:"+rs.getInt(1)+" 用户名:"+rs.getString(2)+" 密码:"+rs.getString(3));
}else{
System.out.println("查询失败");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
DBUtil.get_CloseConn(rs,pstm,conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
Func t = new Func();
User user = new User("李四虎","789789");
t.addUser(user);
}
}
Main主函数类
package com.day1;
import java.util.Scanner;
public class Main {
public static void main(String args[]){
boolean temp = true;
Scanner sc = new Scanner(System.in);
Func tu = new Func();
while(temp){
System.out.println("1.增加用户");
System.out.println("2.删除用户");
System.out.println("3.修改用户");
System.out.println("4.查询用户");
System.out.println("0.退出");
System.out.print("请输入选项:");
int i = sc.nextInt();
if(i == 1){
System.out.print("请输入要添加的用户名:");
String uname = sc.next();
System.out.print("请输入要添加的密码:");
String pword = sc.next();
User user = new User(uname,pword);
tu.addUser(user);
}else if(i == 2){
System.out.print("请输入要删除的用户名:");
String uname = sc.next();
System.out.print("请输入其密码:");
String pword = sc.next();
User user = new User(uname,pword);
tu.DeleteUser(user);
}else if(i == 3){
System.out.print("请输入要修改的用户名:");
String uname = sc.next();
System.out.print("请输入其密码:");
String pword = sc.next();
User user = new User(uname,pword);
tu.UpdateUser(user);
}else if(i == 4){
System.out.print("请输入要查找的用户名:");
String uname = sc.next();
User user = new User(uname);
tu.SearchUser(user);
}else if(i == 0){
System.out.println("退出成功");
temp = false;
}else{
System.out.println("输入错误,请重新输入!");
}
}
}
}
User用户类
package com.day1;
//实体类 映射数据库字段
public class User {
//1.对属性的封装
private int id;
private String username;
private String password;
//2.对get和set 右键generate,选择get and set
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;
}
//3.构造方法 右键generate,选择constructor
public User() {
}
public User(String username) {
this.username = username;
}
public User(String username, String password) {
this.username = username;
this.password = password;
}
public User(int id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
//4.toString方法重写 右键generate,选择toString
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
}
4.效果图