使用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();    }