2020-08-11
JDBC-Java DataBase Connectivity
java操作数据库
Java对外有一套操作数据库的规范:java制定了操作数据库的接口
不同的数据库制作厂商,实现Java规范,即实现接口,完成具体操作
JBDC的步骤
1 注册驱动
2 获得连接对象
3 获得执行语句
4 执行SQL
5 关流
- 创建一个Java project
- 在项目根路径下创建lib文件夹,将mysql-connector-java-5.x.x.jar文件粘贴到此处,统一管理
ps:mysql-connector-java-5.1.46.jar里面是mysql厂商对Java的jbdc做出的事项 - 选中jar包,右键build path–>add to build path Ps:如何移除jar包
- 创建的user表
代码1–执行插入INSERT
语句
package com.zhiyou100.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Demo1 {
/*
* 1 注册驱动
2 获得连接对象
3 获得执行语句
4 执行SQL
5 关流
*/
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1 注册驱动:将MySQL的驱动包,加载到内存
Class.forName("com.mysql.jdbc.Driver");
//2 通过驱动管理获得连接对象
// jdbc协议:mysql协议://IP localhost:端口 3306/库名 java28
String url = "jdbc:mysql://localhost:3306/java28";//8版本的MySQL需要在库名后面添加?&useSSL=flase&serverTimezone=UTC
String username = "root";//MySQL的用户名
String password = "123456";//MySQL的密码
Connection conn = DriverManager.getConnection(url, username, password);
//3 通过连接对象获得执行语句对象
Statement crs = conn.createStatement();
//执行sql
//System.out.println(crs); //判断前3步是否正确
String sql = "INSERT INTO user VALUES(5,'迪丽热巴','女','1990-01-01',100)";
int rowNum = crs.executeUpdate(sql);//增删改都是update,返回值是受影响的行数
if(rowNum>0) {
System.out.println("执行成功");
}
//关流
crs.close();
conn.close();
}
}
结果:
代码2–执行更新(UPDATE
)语句
package com.zhiyou100.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Demo2 {
public static void main(String[] args) throws Exception {
//1
Class.forName("com.mysql.jdbc.Driver");
//2
String url = "jdbc:mysql://localhost:3306/java28";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url,username,password);
//3
Statement s = conn.createStatement();
//将id为5的人的分数改为99
String sql = "update user set score = 99 where id = 5";
int rowNum = s.executeUpdate(sql);//受影响行数
if(rowNum>0) {
System.out.println("更新成功");
}else {
System.out.println("更新失败");
}
s.close();
conn.close();
}
}
结果:
代码3****执行更新(DELETE
)语句
package com.zhiyou100.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Demo3 {
public static void main(String[] args) throws Exception {
// 1
Class.forName("com.mysql.jdbc.Driver");
//2
String url = "jdbc:mysql://localhost:3306/java28";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url,username,password);
Statement s = conn.createStatement();
String sql = "delete from user where id = 5";
int rowNum = s.executeUpdate(sql);//受影响行数
if(rowNum>0) {
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
conn.close();
s.close();
}
}
结果:
查询
package com.zhiyou100.jdbc;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Demo4 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1
Class.forName("com.mysql.jdbc.Driver");
//2
String url = "jdbc:mysql://localhost:3306/java28";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url,username,password);
Statement s = conn.createStatement();
//执行查询会返回一个结果集
String sql ="select * from user";
ResultSet rs = s.executeQuery(sql);//结果集
//判断结果集中有没有下一条数据,有数据返回true,无数据返回false
//next()初始,执行表头及字段,如果表下有数据,即返回true,无数据返回false,next()每执行一次
while(rs.next())
{
int id = rs.getInt(1);
//通过列下(columnIndex)标获得该字段数据,虚拟表的下标从1开始
//注意:是按照虚拟表的字段顺序
System.out.println("id = "+id);
//通过列名(columnLabel)获得该字段数据 注意: 是按照虚拟表的列名,如果有别名,就按照别名
String name = rs.getString("name");
System.out.println("name = "+name);
String sex = rs.getString("sex");
System.out.println("sex = "+sex);
Date date = rs.getDate("birthday");
System.out.println("birthday = "+date);
double score = rs.getDouble("score");
System.out.println("score = "+score);
}
//5
s.close();
conn.close();
}
}
结果:
停电了 ,明天加上。。。。。。。。。。。.。。。