JDBC

1.

在Java工程下建立文件夹lib，放入mysql-connector-java-5.1.38-bin.jar，然后点击右键”add to build path“。

2.

创建与数据库对应的实体类：

package entity;import java.io.Serializable;public class Users implements Serializable{/** *  */private static final long serialVersionUID = -5350994043280204179L;private String id;private String address;private String loginName;private Integer loginPwd;private String name;private String phone;private String mail;public String getId() {return id;}public void setId(String id) {this.id = id;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}public String getLoginName() {return loginName;}public void setLoginName(String loginName) {this.loginName = loginName;}public Integer getLoginPwd() {return loginPwd;}public void setLoginPwd(Integer loginPwd) {this.loginPwd = loginPwd;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}public String getMail() {return mail;}public void setMail(String mail) {this.mail = mail;}public Users() {}public Users(String id, String address, String loginName, Integer loginPwd,String name, String phone, String mail) {super();this.id = id;this.address = address;this.loginName = loginName;this.loginPwd = loginPwd;this.name = name;this.phone = phone;this.mail = mail;}}

3.连接数据库以及测试增删改查等操作：

package util;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import org.junit.Test;import com.mysql.jdbc.Connection;import com.mysql.jdbc.PreparedStatement;import entity.Users;public class DBUtils {private static String DRIVER = "com.mysql.jdbc.Driver";private static String URL="jdbc:mysql://127.0.0.1:3306/grade_db?characterEncoding=utf8&useSSL=true";private static String USER="root";private static String PAWWORD="123456";static Connection conn = null ;static PreparedStatement stmt = null;static ResultSet rst = null;/** * 数据库连接 * @return */private static Connection getConnection(){try {Class.forName(DRIVER);conn = (Connection) DriverManager.getConnection(URL, USER, PAWWORD);} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}System.out.println("get connection succeed");return conn;}/** * 插入数据 * @param users * @return */private static int insert(Users users){Connection conn = getConnection();int i = 0;String sql = "insert into users (address,loginName,loginPwd) values(?,?,?)";try {PreparedStatement pstmt;pstmt = (PreparedStatement) conn.prepareStatement(sql);pstmt.setString(1, users.getAddress());pstmt.setString(2, users.getLoginName());pstmt.setInt(3, users.getLoginPwd());        i = pstmt.executeUpdate();        pstmt.close();        conn.close();} catch (SQLException e) {e.printStackTrace();}return i;}/** * 更新数据 * @param users * @return */private static int update(Users users){Connection conn = getConnection();String sql = "update users set address='"+users.getAddress()+"'where loginPwd='"+users.getLoginPwd()+"'";PreparedStatement pstmt ;int i = 0;try {pstmt = (PreparedStatement) conn.prepareStatement(sql);i = pstmt.executeUpdate();System.out.println("resutl: " + i);pstmt.close();conn.close();} catch (SQLException e) {e.printStackTrace();}return i;}/** * 查询所有 select all * @return */private static Integer getAll(){Connection conn = getConnection();    String sql = "select * from users";    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;}/** * 删除 * @param address * @return */private static int delete(String address) {    Connection conn = getConnection();    int i = 0;    String sql = "delete from users where address='" + address + "'";    PreparedStatement pstmt;    try {        pstmt = (PreparedStatement) conn.prepareStatement(sql);        i = pstmt.executeUpdate();        System.out.println("resutl: " + i);        pstmt.close();        conn.close();    } catch (SQLException e) {        e.printStackTrace();    }    return i;}/** * 测试 */@Testpublic void test(){//测试数据库连接//getConnection();//插入数据//Users users = new Users();//users.setAddress("guangzohu");//users.setLoginName("456789");//users.setLoginPwd(453455);//insert(users);//更新数据//Users users = new Users();//users.setAddress("beijing");//users.setLoginPwd(80);//update(users);//查询所有getAll();}}