JDBC基础知识

1.定义：

JDBC是数据库连接技术，实现对各种数据库的访问

2.优点：

不必使用不同的语法，安装加载不同的数据库驱动就可以

3.JDBC_API连接数据库的常用类：

DriverManager:管理驱动

Connection:连接数据库并传递数据

PreparedStatement:执行sql语句

ResultSet:保存查询后的结果

4.JDBC的使用步骤：

注册JDBC驱动

管理连接

创建PreparedStatement对象

发送sql语句，得到返回结果

处理返回结果

释放资源

5.JDBC编程步骤：

Class.forName()加载驱动

DriverManger.getConnection(url,用户名,密码)获得数据库连接

获得PreparedStatement对象，执行sql语句（增删改不用）查需要保存

处理执行结果，释放资源

6.JDBC连接数据库

package jdbc211019;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Demo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.JDBC注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.管理连接(三个参数：jdbc连接url，数据库用户名，密码)
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","111");
        //3.测试连接
        System.out.println(connection);
        //关闭连接
        connection.close();
    }
}

6.1JDBC添加记录数

package jdbc211019;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Collection;

//使用JDBC添加
public class Demo02 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection collection = DriverManager.getConnection("jdbc:mysql://localhost:3306/work1","root","111");
        //调用collection的prepareStatement对象，发送sql语句
        PreparedStatement preparedStatement = collection.prepareStatement("insert into `t_student`(`name`,age) values(?,?) ");
        //传递参数到sql语句，重复会覆盖
        preparedStatement.setObject(1,"迪丽热巴");
        preparedStatement.setObject(2,"18");
        preparedStatement.setObject(1,"吴宣仪");
        preparedStatement.setObject(2,"18");
        //执行sql语句
        preparedStatement.executeUpdate();
        //关闭资源
        preparedStatement.close();
        collection.close();
    }

6.2JDBC删除数据

package jdbc211019;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

//删除记录
public class Demo03 {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/work1","root","111");
        PreparedStatement ps = connection.prepareStatement("delete from `t_student` where id = ?");
        ps.setObject(1,5);
        ps.executeUpdate();
        ps.close();
        connection.close();
    }
}

6.3JDBC修改

package jdbc211020;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

//jdbc实现修改
public class Demo01 {
    public static void main(String[] args) throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/work1","root","111");
        PreparedStatement ps = conn.prepareStatement("update t_student set `name` = ?, age = ? where id = ?");
        ps.setObject(1,"迪丽热巴");
        ps.setObject(2,20);
        ps.setObject(3,2);
        ps.executeUpdate();
        ps.close();
        conn.close();
    }
}

增删改三者类似，无返回值需要接收，查需要调用返回值

6.4JDBC实现查询

import jdbc211020.bao.User;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Demo02 {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/work1","root","111");
        PreparedStatement ps = conn.prepareStatement("select * from t_student where id = ?");
        ps.setObject(1,2);
        ResultSet rs = ps.executeQuery(); //和增删改不同的方法
        User user = null; //创建实体类来接收
        while (rs.next()){
            user = new User();
            user.setId(rs.getInt("id"));
            user.setName(rs.getString("name"));
            user.setAge(rs.getString("age"));
        }
        rs.close(); //依次关闭
        ps.close();
        conn.close();
        System.out.println(user.getId()+user.getName()+user.getAge());
    }
}