JDBC连接SQL数据库的基本操作

先写一个加载JDBC驱动和创建数据库连接和关闭数据库连接

package second;
import java.sql.*;
public class link {
    static {//加载JDBC
        try{
            String driverName="com.mysql.cj.jdbc.Driver";
            Class.forName(driverName);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public static Connection getConnection(){//创建数据库连接
        Connection con=null;
        try {
            con=DriverManager.getConnection("jdbc:mysql://localhost/tudou","root","");
        }catch (Exception e){
            e.printStackTrace();
        }
        return  con;
    }
    public static void close(ResultSet rs,Connection con,Statement statement,PreparedStatement pstmt){//关闭数据库连接
        try {
            if(rs !=null)rs.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        try {
            if(statement !=null) statement.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        try {
            if(con !=null) con.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        try {
            if(pstmt !=null) pstmt.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

}

再写一个用java实现关于sql中的增删改查的功能

package second;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.text.ParseException;

public class demof{
    Connection con;//数据库连接
    ResultSet rs;//保存查询结果
    Statement statement;//数据库操作
    PreparedStatement pstmt;

    public Statement getStatement() {
        return statement;
    }

    public ResultSet getRs() {
        return rs;
    }

    public Connection getCon() {
        return con;
    }

    public PreparedStatement getPstmt() {
        return pstmt;
    }

    public demof(Connection con){
        this.con=con;
        try {
            statement=con.createStatement();//实例化Statement对象
        }catch (SQLException e){
            e.printStackTrace();
        }
    }
    public void createTable() throws  SQLException{//创建表
        try{
            String sql="create table if not exists test1(id int,age int,name varchar(100))";
            statement.executeUpdate(sql);
        }catch (SQLException e){
            e.printStackTrace();
        }
    }
    public void  Insert()throws SQLException{//插入
        String sql1="insert into test1 values(1,19,'土豆啊土豆')";
        String sql2 ="insert into test1 values(2,20,'土豆呀土豆')";
        String sql3="insert into test1 values(3,21,'土豆和土豆')";
        String sql4="insert into test1 values(?,?,?)";//PreparedStatement操作
        pstmt=con.prepareStatement(sql4);
        pstmt.setInt(1,4);
        pstmt.setInt(2,25);
        pstmt.setString(3,"你好呀");
        statement.addBatch(sql1);
        statement.addBatch(sql2);
        statement.addBatch(sql3);
        int[] results=statement.executeBatch();
        pstmt.executeUpdate();
    }
    public void Select() throws SQLException{//查询
        String sql="select id,age,name from test1";//查询不能使用*在实际开发中必须明确写出查询列
        rs=statement.executeQuery(sql);
        while(rs.next()){
            String id=rs.getString("id");
            int age=rs.getInt("age");
            String name=rs.getString("name");
            System.out.println(id+"\t"+age+"\t"+name);
        }

    }
    public void Delete() throws  SQLException{//删除
        String sql="delete from test1 where id=2";
        statement.executeUpdate(sql);
    }
    public static void main(String[] args) {
        Connection con=link.getConnection();
        demof start=new demof(con);
        try {
            start.createTable();
//            start.Insert();
//            start.Select();
            //start.Delete();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
           link.close(start.getRs(),start.getCon(),start.getStatement(),start.getPstmt());
        }
    }
}