使用JDBC操作Mysql数据库

一、下载对应jar包并导入当前工程

1、下载对应jar包

2、导入

3、在代码中导入包

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

二、创建表的对应实体类

为了方便操作而创建

public class Teacher {
    private String id;
    private String tname;
    private String sex;
    private String did;
    private String age;
    private String workage;

    public Teacher(String id, String tname, String sex, String did, String age,String workage) {
        super();
        this.id = id;
        this.tname = tname;
        this.sex = sex;
        this.did = did;
        this.age = age;
        this.workage = workage;
    }

    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getTname() {
        return tname;
    }
    public void setTname(String tname) {
        this.tname = tname;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public String getDid() {
        return did;
    }
    public void setDid(String did) {
        this.did = did;
    }
    public String getAge() {
        return age;
    }
    public void setAge(String age) {
        this.age = age;
    }
    public String getWorkage() {
        return workage;
    }
    public void setWorkage(String workage) {
        this.workage = workage;
    }

}

三、获取与数据库的连接

driver、url的格式参照下图：

    private static Connection getConn() {
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/xxx";
        String username = "root";
        String password = "xxx";
        Connection conn = null;
        try {
            Class.forName(driver); 
            conn = (Connection) DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

四、增

private static int insert(Teacher  teacher) {
        Connection conn = getConn();
        int i = 0;
        String sql = "insert into teacher (id,tname,sex,did,age,work_age) values(?,?,?,?,?,?)";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setString(1, teacher.getId());
            pstmt.setString(2, teacher.getTname());
            pstmt.setString(3, teacher.getSex());
            pstmt.setString(4, teacher.getDid());
            pstmt.setString(5, teacher.getAge());
            pstmt.setString(6, teacher.getWorkage());
            i = pstmt.executeUpdate();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

五、删

private static int delete(String id) {
        Connection conn = getConn();
        int i = 0;
        String sql = "delete from teacher where id='" + id + "'";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            i = pstmt.executeUpdate();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

}

六、改

    private static int update(Teacher teacher) {
        Connection conn = getConn();
        int i = 0;
        String sql = "update teacher set age='" + teacher.getAge() + "' where id='" + teacher.getId() + "'";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            i = pstmt.executeUpdate();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

七、查

    private static Integer select() {
        Connection conn = getConn();
        String sql = "select * from teacher";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement)conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            int col = rs.getMetaData().getColumnCount();
            System.out.println("============================");
            while (rs.next()) {
                for (int i = 1; i <= col; i++) {
                    System.out.print(rs.getString(i) + "\t");
                    if ((i == 2) && (rs.getString(i).length()<8)){
                        System.out.print("\t");
                    }
                 }
                System.out.println("");
            }
                System.out.println("============================");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

八、测试

只对数据库中的一个表(teacher)进行了操作

    public static void main(String[] args) {
        insert(new Teacher("13", "tom", "m", null, "10","30"));
        delete("11");
        update(new Teacher("13","","","","8",""));
        select();
    }