1.jdbc七大操作步骤的关于API的介绍
java.sql.Drvier 驱动接口
java.sql.DriverManager:驱动管理类(管理jdbc的驱动服务)
java.sql.Connection:与特定数据库的一种会话连接
java.sql.Statement:执行静态sql语句 (执行对象,操作数据库)
java.sql.ResultSet:获取数据表的结果集 (接口)
1)导包并且注册驱动
Class.forName("com.mysql.jdbc.Driver) ; //mysql5.5对应5.1jar包
这样写的目的是为了保证向后的兼容性:就是目的加载这个类!
本身注册驱动使用的是DriverManager:管理jdbc的驱动服务
方法:public static void registerDriver(Driver driver) throws SQLException
方法的形式参数是接口,需要接口的子实现类对象
接口:java.sql.Drvier
但是不需要使用这种写法,因为驱动jar包已经完成一件事情,注册驱动
public class Driver implements java.sql.Driver {
// 类一加载的时候,就会执行静态代码块,注册驱动
static {
try {
java.sql.DriverManager.registerDriver(new Driver());
} catch (SQLException E) {
throw new RuntimeException("Can't register driver!");
}
}
}
2)获取数据库的连接对象
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/myee_2203_02",
"root",
"123456"
);
涉及一个类java.sql.DrivarManager:驱动管理类
//获取数据库的连接对象,返回值是java.sql.Connection
public static Connection getConnection(String url, String user, String password) throws SQLException
参数1:url 统一自定义定位符
url: jdbc:mysql://域名:端口号/库名
参数2:user: 登录mysql的用户,默认都是管理员用户
参数3:password:登录mysql的密码
java.sql.Connection:与特定数据库的一种会话连接 :连接对象去获取描述表的信息等等
3)获取执行对象
// 获取执行对象
Statement createStatement() throws SQLException
// 获取预编译对象,并且将sql发送给数据库进行预编译
PreparedStatement prepareStatement(String sql) throws SQLException
java.sql.Statement:执行静态sql语句,针对DDL语句(建表),DML语句(insert into,delete ,update)
insert into 表名 values(值1,值2,值3…) ; sql语句直接写死 执行SQL语句
4)执行sql语句
// 通用的方法:
int executeUpdate(String sql)//通用的更新操作 针对DDL,DML语句
ResultSet executeQuery(String sql)//针对查询语句的操作 DQL语句
java.sql. PreparedStatement extends java.sql.Statement :预编译对象,
insert into 表名 values(?,?,?,?..) ; 预编译的sql语句
int executeUpdate():通用的更新操作 针对DDL,DML语句
ResultSet executeQuery():针对查询语句的操作 DQL语句
java.sql.ResultSet:表示数据库结果集的数据表,通常通过执行查询数据库的语句生成。
2.使用Statement对象完成DDL语句以及DML语句操作
2.1 使用Statement对象完成DDL语句
public class Jdbc_OperatrDDL {
public static void main(String[] args) throws Exception{
//1)导包,注册驱动
Class.forName("com.mysql.jc.jdbc.Driver") ;
//2)获取数据库连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myee_2203_02",
"root",
"123456");
//3)准备sql
String sql = "CREATE TABLE student("
+ " id INT PRIMARY KEY AUTO_INCREMENT," +
" NAME VARCHAR(10), " +
" gender VARCHAR(2), " +
" address VARCHAR(50), " +
" hobby VARCHAR(100) " +
") " ;
//4)使用连接对象创建执行对象
Statement stmt = connection.createStatement();
//5)执行sql,发送数据库
int count = stmt.executeUpdate(sql);
System.out.println("影响"+count+"行") ;
//6)释放资源
stmt.close();
connection.close();
}
}
2.2 使用Statement对象完成DML语句
public class JDBC_Operator_DML {
public static void main(String[] args) throws Exception {
//注册驱动
Class.forName("com.mysql.jc.jdbc.Driver") ;
//获取数据库连接对象
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/myee_2203_02",
"root",
"123456"
) ;
//sql
String sql = "insert into student(name,gender,address,hobby) values('张佳宁','女','鄠邑区','演戏')," +
"('刘翔','男','西安市','跑步')," +
"('小虎队','男','西安市','唱歌');" ; ///静态sql语句
//修改
//String sql = "update student set name = '周杰伦' where id = 3" ; //静态sql语句
//删除
//String sql = "delete from student where id = 3" ;//静态sql语句
//连接对象获取执行对象
Statement stmt = conn.createStatement();
//执行sql,发送数据库
int count = stmt.executeUpdate(sql) ;
System.out.println("影响了"+count+"行") ;
//释放资源
stmt.close();
conn.close();
}
}
3.使用Statement完成DQL语句的操作
java.sql.ResultSet:获取数据表的结果集 (接口) api的使用
public class Jdbc_Operator_DQL {
public static void main(String[] args)throws Exception {
//需求:查询myee_2203_02 库中学生表的所有信息
//注册驱动
Class.forName("com.mysql.jdbc.Driver") ;
//获取数据库的连接对象
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/myee_2203_02",
"root",
"123456"
);
//sql
String sql ="select * from student" ;
//获取执行对象
Statement stmt = connection.createStatement();
//执行DQL语句
//ResultSet exceuteQuery(String sql)
ResultSet rs = stmt.executeQuery(sql);
System.out.println("学生列表如下:");
System.out.println("编号\t姓名\t性别\t地址\t爱好");
/* //第一次获取
if(rs.next()){ //true,有数据,光标向前移动一行
//获取数据 ResultSet 通用操作
//xxx getXxx(int 列的索引值)
//xxx getXxxx(String 列的名称)
int id = rs.getInt(1); //获取编号
String name = rs.getString(2); //获取姓名
String gender = rs.getString(3); //获取性别
String addrses = rs.getString(4); //获取地址
String hobby = rs.getString(5);//获取爱好
System.out.println(id+"\t\t"+name+"\t"+gender+"\t\t"+addrses+"\t"+hobby);
}
//第二次获取
if(rs.next()){ //true,有数据,光标向前移动一行
//获取数据 ResultSet 通用操作
//xxx getXxx(int 列的索引值)
//xxx getXxxx(String 列的名称)
int id = rs.getInt("id"); //获取编号
String name = rs.getString("name"); //获取姓名
String gender = rs.getString("gender"); //获取性别
String addrses = rs.getString("address"); //获取地址
String hobby = rs.getString("hobby");//获取爱好
System.out.println(id+"\t\t"+name+"\t"+gender+"\t\t"+addrses+"\t"+hobby);
}*/
//依次类推
//不明确循环次数
while(rs.next()){
int id = rs.getInt("id"); //获取编号
String name = rs.getString("name"); //获取姓名
String gender = rs.getString("gender"); //获取性别
String addrses = rs.getString("address"); //获取地址
String hobby = rs.getString("hobby");//获取爱好
System.out.println(id+"\t\t"+name+"\t"+gender+"\t\t"+addrses+"\t"+hobby);
}
//释放资源
rs.close();
stmt.close();
connection.close();
}
}
4.关于Statement完成DQL语句的应用
一个学生表,学生表很多条记录,使用Statement查询学生表,将查询出每一条记录封装到学生类中,然后将每一个学生类的数据,添加到List,将List中每一个学生的数据遍历出来!
class Student{ private String name; //属性都是小写 }
代码:
public class Creatr_Student {
public static void main(String[] args) {
ArrayList<Student> list = new ArrayList<>();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "select id,name,age,gender,address from student";
stmt =conn.createStatement();
rs = stmt.executeQuery(sql);
while(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String gender = rs.getString("gender");
String address = rs.getString("address");
list.add(new Student(id,name,age,gender,address));
}
} catch (SQLException e) {
e.printStackTrace();
}
JdbcUtils.close(rs,stmt,conn);
for (Student student : list) {
System.out.println(student);
}
}
}
//student类
class Student{
private int id;
private String name;
private int age;
private String gender;
private String address;
public Student(int id, String name, int age, String gender, String address) {
this.id = id;
this.name = name;
this.age = age;
this.gender = gender;
this.address = address;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", gender='" + gender + '\'' +
", address='" + address + '\'' +
'}';
}
public Student() {
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
5.使用Statement模拟用户(用户名和登录)登录操作
-- SQL代码
CREATE TABLE USER(
id INT,
username VARCHAR(20),
PASSWORD VARCHAR(20)
);
INSERT INTO USER VALUES
(1,'rose','123'),(2,'jack','123456');
SELECT * FROM USER;
// Java代码
public class Test {
public static void main(String[] args) {
if(opinion()){
System.out.println("登陆成功");
}else{
System.out.println("登陆失败");
}
}
public static boolean opinion(){
Scanner scanner = new Scanner(System.in);
System.out.println("请输入账户:");
String username = scanner.next();
System.out.println("请输入密码:");
String password = scanner.next();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
int i = 0;
try {
conn = JDBCutils.getConnection();
stmt = conn.createStatement();
String sql = "select * from user";
rs = stmt.executeQuery(sql);
while(rs.next()){
String usernamae1 = rs.getString(2);
String password1 = rs.getString(3);
if(username.equals(usernamae1)&&password.equals(password1)){
return true;
}
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCutils.close(rs,stmt,conn);
}
return false;
}
}
//自己编写的工具类
public class JDBCutils {
private static String driverClassname = null;
private static String url = null;
private static String username = null;
private static String password = null;
static{
try {
Properties p = new Properties();
InputStream is = exercise_jdbc.JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
p.load(is);
driverClassname = p.getProperty("driverClassname");
url = p.getProperty("url");
username = p.getProperty("username");
password = p.getProperty("password");
Class.forName(driverClassname);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private JDBCutils(){}
public static Connection getConnection() throws SQLException {
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
//释放资源
public static void close(Statement stmt, Connection conn){
close(null,stmt,conn);
}
public static void close(ResultSet rs, Statement stmt, Connection conn){
if (rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}