- 向数据库添加数据
//加载数据库驱动
private static final String driverClass = "com.mysql.cj.jdbc.Driver";
//连接远程数据库地址
private static final String url = "jdbc:mysql://127.0.0.1:3306/stu_dba?srverTimezone=Asia/Shanghai";
//数据库用户名
private static final String user = "root";
//数据库密码
private static final String password = "0415";
//静态代码块 随着类的加载而加载 只加载一次
static {
try {
//加载JDBC驱动程序
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.out.println("加载MySQL驱动类失败~~~");
}
}
//获取数据库连接对象
public Connection getConnection(){
Connection con = null;
try {
/*
DriverManager(驱动管理类):
管理不同关系型数据库(比如:Oracle,MySQL,SQL,Server等)驱动程序大管家
*/
con = DriverManager.getConnection(url,user,password);
return con;
} catch (SQLException e) {
e.printStackTrace();
System.out.println("操作数据库出错~~~");
return null;
}
}
//释放所有资源
public void closeAll(ResultSet rs, PreparedStatement stmt, Connection con){
if (null != rs){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (null != stmt){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (null != con){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/*
访问权限
*/
public static void main(String[] args) throws SQLException {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入姓名:");
String name = scanner.next();
System.out.println("请输入性别(0:保密 1:男 2:女):");
int gender = scanner.nextInt();
System.out.println("请输入出生日期:");
String birthDate = scanner.next();
System.out.println("请输入手机号:");
String phone = scanner.next();
System.out.println("请输入邮箱:");
String email = scanner.next();
System.out.println("请输入地址:");
String address = scanner.next();
System.out.println("请输入密码:");
String password = scanner.next();
System.out.println("请输入身份证号码:");
String idNumber = scanner.next();
System.out.println("请输入班级:1:java 2:html 3:css 4:springbolod 5:javascorpt");
int classId = scanner.nextInt();
jdbc_02 _db = new jdbc_02();
//获取数据库连接对象
Connection conn = _db.getConnection();
System.out.println(conn.isClosed() == false ? "连接成功~" : "连接失败~");
String sql = "insert into student(name,gender,birth_date,phone,email,address,password,id_number,class_id)values(?,?,?,?,?,?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,name);
ps.setInt(2, gender);
ps.setString(3,birthDate);
ps.setString(4,phone);
ps.setString(5,email);
ps.setString(6,address);
ps.setString(7,password);
ps.setString(8,idNumber);
ps.setInt(9, classId);
//executeUpdate() 执行DML语句,返回受DML语句影响的行数,如果非0
int rows = ps.executeUpdate();
if (rows>0){
System.out.println("添加学生成功~");
}
_db.closeAll(null,ps,conn);
}
- 向数据库删除数据
// 驱动程序类
private static final String driverClass = "com.mysql.cj.jdbc.Driver";
//数据库连接地址
//private static final String url = "jdbc:mysql:///stu_db?serverTimezone=Asia/Shanghai";
private static final String url = "jdbc:mysql://127.0.0.1:3306/stu_db?serverTimezone=Asia/Shanghai";
//数据库用户名
private static final String user = "root";
//密码
private static final String password = "root";
// 静态代码块 :随着类的加载而加载,仅执行一次
static {
try {
//加载驱动程序类(mysql)
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.out.println("加载MySQL驱动类失败");
}
}//Excel表格?
//获得数据库连接对象
public Connection getConnection() {
try {
/*
DriverManager(驱动管理类):
管理不同关系型数据库(比如:Oracle、MySQL、SQL Server等)驱动程序大管家。
*/
Connection conn =
DriverManager.getConnection(url,user,password);
return conn;
} catch (SQLException e) {
e.printStackTrace();
System.out.println("操作数据库出错");
return null;
}
}
//释放所有资源
public void closeAll(ResultSet rs, Statement stmt,Connection conn){
if(null != rs) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null != stmt) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null != conn) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) throws SQLException {
Scanner input = new Scanner(System.in);
System.out.println("请输入学号:");//通过主键(学号)或唯一键来作为删除条件
Integer id = input.nextInt();
Jdbc_04 _db = new Jdbc_04();
//获取数据库连接对象
Connection conn = _db.getConnection();
System.out.println(conn.isClosed() == false ? "连接成功!" : "连接失败");
String sql = "delete from student where id=?";
PreparedStatement ps = conn.prepareStatement(sql);
// 给占位符?设置参数值
ps.setInt(1,id);
//executeUpdate()执行DML语句,返回受DML语句影响的行数,如果非0,表示成功,否则失败
int rows = ps.executeUpdate();
if(rows > 0) {
System.out.println("删除学生成功!");
}
_db.closeAll(null, ps, conn);
}
- 修改(更新)数据库数据
//加载数据库驱动
private static final String driverClass = "com.mysql.cj.jdbc.Driver";
//连接远程数据库地址
private static final String url = "jdbc:mysql://127.0.0.1:3306/stu_dba?srverTimezone=Asia/Shanghai";
//数据库用户名
private static final String user = "root";
//数据库密码
private static final String password = "0415";
//静态代码块 随着类的加载而加载 只加载一次
static {
try {
//加载JDBC驱动程序
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.out.println("加载MySQL驱动类失败~~~");
}
}
//获取数据库连接对象
public Connection getConnection(){
Connection con = null;
try {
/*
DriverManager(驱动管理类):
管理不同关系型数据库(比如:Oracle,MySQL,SQL,Server等)驱动程序大管家
*/
con = DriverManager.getConnection(url,user,password);
return con;
} catch (SQLException e) {
e.printStackTrace();
System.out.println("操作数据库出错~~~");
return null;
}
}
//释放所有资源
public void closeAll(ResultSet rs, PreparedStatement stmt, Connection con){
if (null != rs){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (null != stmt){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (null != con){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/*
访问权限
*/
public static void main(String[] args) throws SQLException {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入学号:");
int id = scanner.nextInt();
System.out.println("请输入出生日期:");
String birthDate = scanner.next();
System.out.println("请输入手机号:");
String phone = scanner.next();
System.out.println("请输入邮箱:");
String email = scanner.next();
jdbc_03 _db = new jdbc_03();
//获取数据库连接对象
Connection conn = _db.getConnection();
System.out.println(conn.isClosed() == false ? "连接成功~" : "连接失败~");
String sql = "update student set birth_date = ? ,phone = ?,email = ? where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
//给占位符?设置参数值
ps.setString(1,birthDate);
ps.setString(2, phone);
ps.setString(3,email);
ps.setInt(4,id);
//executeUpdate() 执行DML语句,返回受DML语句影响的行数,如果非0
int rows = ps.executeUpdate();
if (rows>0){
System.out.println("更新学生成功~");
}
_db.closeAll(null,ps,conn);
}
注:应对某些方法进行了封装,所以我们在操作的时候只需要更改一下sql语句还有相对应的值即可,其他代码都是一样