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;
}
/**
* 测试
*/
@Test
public 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();
}
}