目录
2.进入IDEA中在你建立的项目里边右键点击Directory建立lib
建了一个群:908722740 ,欢迎小伙伴门的加入,平时可以互相学习交流,不管是学习还是工作上的都可以多多交流,本人在校学生,技术有限,错误的地方欢迎指正,里边用到的各类安装包可以评论,我发你
一、在Navicat premium中创建表
1、表的设计
特别注意:我们可以把id默认设置为自动递增
2、表的创建
二、在IDEA中导入jar包
1.把这个文件复制下来
2.进入IDEA中在你建立的项目里边右键点击Directory建立lib
3.把第一步复制的jar粘贴到 lib 包下
4.选中导入进来的jar右键点击Add as,,
5.点击完之后如果是下图这个效果,说明导入完成
三、实现增删改查功能
1.查
/**
* 查询
*
* @throws SQLException
* @throws ClassNotFoundException
*/
public static void getDt() throws SQLException, ClassNotFoundException {
//1.手动加载数据库驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
//2.建立与数据库的连接,获得连接对象
String username = "root";
String password = "root";
String url = "jdbc:mysql://localhost:3306/jr?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
Connection con = DriverManager.getConnection(url, username, password);
System.out.println(con);
//3.写sql命令
String sql = "select * from dvd";
//4.获得执行对象。
PreparedStatement ps = con.prepareStatement(sql);
//5.执行sql命令,同时获得结果
ResultSet rs = ps.executeQuery();
//6.处理结果
while (rs.next()) {//判断结果集对象中是否还有下一行数据
System.out.println(rs.getInt("id") + "\t" + rs.getString(2) + "\t"
+ rs.getString(3) + "\t" + rs.getDouble(4) + "\t"
+ rs.getString(5) + "\t" + rs.getInt(6));
}
//7.关闭所有连接资源
rs.close();
ps.close();
con.close();
}
运行截图
2.增
/**
* 添加
*
* @throws ClassNotFoundException
* @throws SQLException
*/
public static void addDVD() throws ClassNotFoundException, SQLException {
Scanner input=new Scanner(System.in);
//1.手动加载数据库驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
//2.建立与数据库的连接,获得连接对象
String username = "root";
String password = "root";
String url = "jdbc:mysql://localhost:3306/jr?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
Connection con = DriverManager.getConnection(url, username, password);
//3.写sql语句
String sql = "insert into dvd(name,state,money,count) values(?,'可借',?,0)";
//接下来给两个问好赋值,在要在第五步前,给两个问号赋值就行
//4.获得执行对象
PreparedStatement ps = con.prepareStatement(sql);
System.out.println("请输入要添加的DVD名称");
String name=input.next();
System.out.println("请输入DVD的日租金");
double money=input.nextDouble();
ps.setString(1,name);
ps.setDouble(2,money);
//5.执行sql语句同时获得结果
int i = ps.executeUpdate();
//6.处理结果
if (i == 1) {
System.out.println("添加DVD成功");
} else {
System.out.println("添加DVD失败");
}
//7.关闭连接资源
ps.close();
con.close();
}
运行截图
3.删除
/**
* 删除
* @throws ClassNotFoundException
* @throws SQLException
*/
public static void delDVD() throws ClassNotFoundException, SQLException {
//1.手动加载数据库驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
//2.建立与数据库的连接,获得连接对象
String username = "root";
String password = "root";
String url = "jdbc:mysql://localhost:3306/jr?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
Connection con = DriverManager.getConnection(url, username, password);
//3.写sql语句
String sql="delete from dvd where name=?";
//4.获得执行对象
PreparedStatement ps=con.prepareStatement(sql);
System.out.println("请输入要删除的DVD名称");
String name=new Scanner(System.in).next();
ps.setString(1,name);
//5.执行命令同时获得结果
int i = ps.executeUpdate();
//6.处理结果
if (i==1){
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
//7.关闭资源
ps.close();
con.close();
}
运行截图:
4.修改
/**
* 修改
* @throws ClassNotFoundException
* @throws SQLException
*/
public static void upDVD() throws ClassNotFoundException, SQLException {
Scanner input=new Scanner(System.in);
//1.手动加载数据库驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
//2.建立与数据库的连接,获得连接对象
String username = "root";
String password = "root";
String url = "jdbc:mysql://localhost:3306/jr?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
Connection con = DriverManager.getConnection(url, username, password);
//3.写入sql语句
String sql="update dvd set state=?,lendDate=?,count=count+1 where name=?";
System.out.println("请输入要修改的DVD名称");
String name=input.next();
System.out.println("请输入修改后的状态");
String state=input.next();
System.out.println("请输入修改后的时间");
String dt=input.next();
//4.获得执行对象
PreparedStatement ps=con.prepareStatement(sql);
ps.setString(1,state);
ps.setString(2,dt);
ps.setString(3,name);
//5.执行命令同时获得结果
int i=ps.executeUpdate();
//6.处理结果
if (i>0){
System.out.println("修改成功");
}else {
System.out.println("修改失败");
}
//7.关闭资源
ps.close();
con.close();
}
运行截图:
源码:
package com.jr.dz327;
import java.sql.*;
import java.util.Scanner;
public class Demo1 {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
upDVD();
getDt();
}
/**
* 修改
* @throws ClassNotFoundException
* @throws SQLException
*/
public static void upDVD() throws ClassNotFoundException, SQLException {
Scanner input=new Scanner(System.in);
//1.手动加载数据库驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
//2.建立与数据库的连接,获得连接对象
String username = "root";
String password = "root";
String url = "jdbc:mysql://localhost:3306/jr?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
Connection con = DriverManager.getConnection(url, username, password);
//3.写入sql语句
String sql="update dvd set state=?,lendDate=?,count=count+1 where name=?";
System.out.println("请输入要修改的DVD名称");
String name=input.next();
System.out.println("请输入修改后的状态");
String state=input.next();
System.out.println("请输入修改后的时间");
String dt=input.next();
//4.获得执行对象
PreparedStatement ps=con.prepareStatement(sql);
ps.setString(1,state);
ps.setString(2,dt);
ps.setString(3,name);
//5.执行命令同时获得结果
int i=ps.executeUpdate();
//6.处理结果
if (i>0){
System.out.println("修改成功");
}else {
System.out.println("修改失败");
}
//7.关闭资源
ps.close();
con.close();
}
/**
* 删除
* @throws ClassNotFoundException
* @throws SQLException
*/
public static void delDVD() throws ClassNotFoundException, SQLException {
//1.手动加载数据库驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
//2.建立与数据库的连接,获得连接对象
String username = "root";
String password = "root";
String url = "jdbc:mysql://localhost:3306/jr?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
Connection con = DriverManager.getConnection(url, username, password);
//3.写sql语句
String sql="delete from dvd where name=?";
//4.获得执行对象
PreparedStatement ps=con.prepareStatement(sql);
System.out.println("请输入要删除的DVD名称");
String name=new Scanner(System.in).next();
ps.setString(1,name);
//5.执行命令同时获得结果
int i = ps.executeUpdate();
//6.处理结果
if (i==1){
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
//7.关闭资源
ps.close();
con.close();
}
/**
* 添加
*
* @throws ClassNotFoundException
* @throws SQLException
*/
public static void addDVD() throws ClassNotFoundException, SQLException {
Scanner input=new Scanner(System.in);
//1.手动加载数据库驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
//2.建立与数据库的连接,获得连接对象
String username = "root";
String password = "root";
String url = "jdbc:mysql://localhost:3306/jr?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
Connection con = DriverManager.getConnection(url, username, password);
//3.写sql语句
String sql = "insert into dvd(name,state,money,count) values(?,'可借',?,0)";
//接下来给两个问好赋值,在要在第五步前,给两个问号赋值就行
//4.获得执行对象
PreparedStatement ps = con.prepareStatement(sql);
System.out.println("请输入要添加的DVD名称");
String name=input.next();
System.out.println("请输入DVD的日租金");
double money=input.nextDouble();
ps.setString(1,name);
ps.setDouble(2,money);
//5.执行sql语句同时获得结果
int i = ps.executeUpdate();
//6.处理结果
if (i == 1) {
System.out.println("添加DVD成功");
} else {
System.out.println("添加DVD失败");
}
//7.关闭连接资源
ps.close();
con.close();
}
/**
* 查询
*
* @throws SQLException
* @throws ClassNotFoundException
*/
public static void getDt() throws SQLException, ClassNotFoundException {
//1.手动加载数据库驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
//2.建立与数据库的连接,获得连接对象
String username = "root";
String password = "root";
String url = "jdbc:mysql://localhost:3306/jr?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
Connection con = DriverManager.getConnection(url, username, password);
//3.写sql命令
String sql = "select * from dvd";
//4.获得执行对象。
PreparedStatement ps = con.prepareStatement(sql);
//5.执行sql命令,同时获得结果
ResultSet rs = ps.executeQuery();
//6.处理结果
while (rs.next()) {//判断结果集对象中是否还有下一行数据
System.out.println(rs.getInt("id") + "\t" + rs.getString(2) + "\t"
+ rs.getString(3) + "\t" + rs.getDouble(4) + "\t"
+ rs.getString(5) + "\t" + rs.getInt(6));
}
//7.关闭所有连接资源
rs.close();
ps.close();
con.close();
}
}