点击查看eclipse连接SQL的增删改查
1.首先在数据库中建一个表
CREATE TABLE
Users
(
id int NOT NULL AUTO_INCREMENT,
user_name char(20) NOT NULL,
age int NOT NULL,
sex int NOT NULL,
create_dt DATE NOT NULL,
PRIMARY KEY (id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入数据
insert into Users values(1,'zhangsan',25,1,'2016-10-1');
insert into Users values (2,'lisi',23,1,'2016-10-2');
insert into Users values(3,'wangwu',21,0,'2016-10-3');
打开eclipse,新建一个Java项目(这里已经连接)
定义一个Bean
import java.util.Date;
public class UserVO {
private int id;
private String userName;
private int age;
private int sex;
private Date createDt;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex = sex;
}
public Date getCreateDt() {
return createDt;
}
public void setCreateDt(Date createDt) {
this.createDt = createDt;
}
@Override
public String toString() {
return "UserVO [id=" + id + ", userName=" + userName + ", age=" + age
+ ", sex=" + sex + ", createDt=" + createDt + "]";
}
}
改造Util类,用于获取Mysql连接(本文使用的是5.0版本MySQL。有些高版本代码没问题但仍会报错,可能是时区问题,解决起来不难,百度解决方法很多,可以自己动手试试)
import java.sql.Connection;
import java.sql.DriverManager;
public class DBUtil {
private static final String URI = "jdbc:mysql://localhost:3306/test";//localhost是本地服务器,3306端口,test是数据库名
private static final String user = "root";//数据库账户名,一般默认root
private static final String password = "123";//数据库登陆密码
private static final String DRIVER = "com.mysql.jdbc.Driver";
public static Connection connectDB() throws Exception {
//1、加载数据库驱动
Class.forName(DRIVER);
//2、获取数据库连接
Connection conn = DriverManager.getConnection(URI,user,password);
return conn;
}
}
以上都是准备工作,完成后,定义一个Dao类,在Dao类中实现对数据库的操作
1.查询全部
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class UserDao {
public List<UserVO> queryAll() throws Exception {
Connection conn = DBUtil.connectDB();
String sql = "SELECT * FROM Users";
List<UserVO> userList = new ArrayList<UserVO>();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
UserVO user = new UserVO();
user.setId(rs.getInt("id"));
user.setUserName(rs.getString("user_name"));
user.setAge(rs.getInt("age"));
user.setSex(rs.getInt("sex"));
user.setCreateDt(rs.getDate("create_dt"));
userList.add(user);
}
return userList;
}
public static void main(String[] args) {
UserDao dao = new UserDao();
try {
List<UserVO> userList = dao.queryAll();
for(UserVO user : userList) {
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
查询结果:
2.条件查询:
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class UserDao {
public List<UserVO> queryByParams(List<Map<String, Object>> params) throws Exception {
Connection conn = DBUtil.connectDB();
StringBuilder sql = new StringBuilder("SELECT * FROM Users WHERE 1=1 ");
for(Map<String, Object> param : params) {
sql.append(" and ");
sql.append(" " + param.get("col") + " ");
sql.append(" " + param.get("rel") + " ");
sql.append(" " + param.get("value") + " ");
}
System.out.println(sql.toString());
List<UserVO> userList = new ArrayList<UserVO>();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql.toString());
while(rs.next()) {
UserVO user = new UserVO();
user.setId(rs.getInt("id"));
user.setUserName(rs.getString("user_name"));
user.setAge(rs.getInt("age"));
user.setSex(rs.getInt("sex"));
user.setCreateDt(rs.getDate("create_dt"));
userList.add(user);
}
return userList;
}
public static void main(String[] args) {
UserDao dao = new UserDao();
List<Map<String, Object>> params = new ArrayList<Map<String,Object>>();
Map<String, Object> param1 = new HashMap<String, Object>();
param1.put("col", "user_name");
param1.put("rel", "like");
param1.put("value", "'%ang%'");
params.add(param1);
Map<String, Object> param2 = new HashMap<String, Object>();
param2.put("col", "age");
param2.put("rel", "=");
param2.put("value", 25);
params.add(param2);
try {
List<UserVO> userList = dao.queryByParams(params);
for(UserVO user : userList) {
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
查询结果:
3.插入数据
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
public class UserDao {
public void addUser(UserVO user) throws Exception {
Connection conn = DBUtil.connectDB();
String sql = "INSERT INTO Users(user_name, age, sex, create_dt) "
+ " VALUES(?, ?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, user.getUserName());
pstmt.setInt(2, user.getAge());
pstmt.setInt(3, user.getSex());
pstmt.setDate(4, new Date(new java.util.Date().getTime()));
pstmt.execute();
}
public static void main(String[] args) {
UserDao dao = new UserDao();
UserVO user = new UserVO();
user.setUserName("zhaosi");
user.setAge(20);
user.setSex(0);
try {
dao.addUser(user);
} catch (Exception e) {
e.printStackTrace();
}
}
}
插入结果:
4.删除数据
import java.sql.Connection;
import java.sql.PreparedStatement;
public class UserDao {
public void deleteUser(int id) throws Exception {
Connection conn = DBUtil.connectDB();
String sql = "DELETE FROM Users WHERE id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.execute();
}
public static void main(String[] args) {
UserDao dao = new UserDao();
try {
dao.deleteUser(2);
} catch (Exception e) {
e.printStackTrace();
}
}
}
删除结果:
5.更新数据库
import java.sql.Connection;
import java.sql.PreparedStatement;
public class UserDao {
public void updateUser(UserVO user) throws Exception {
Connection conn = DBUtil.connectDB();
String sql = "UPDATE Users SET id=?, age=?, sex=?"
+ " WHERE user_name=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, user.getId());
pstmt.setInt(2, user.getAge());
pstmt.setInt(3, user.getSex());
pstmt.setString(4, user.getUserName());
pstmt.executeUpdate();
}
public static void main(String[] args) {
UserDao dao = new UserDao();
UserVO user = new UserVO();
user.setId(2);
user.setAge(23);
user.setSex(0);
user.setUserName("zhangsan");
try {
dao.updateUser(user);
} catch (Exception e) {
e.printStackTrace();
}
}
}
将zhangsan的id改为2,年龄改为23,性别改为0,结果如下:
整体结构如下:
学习中,欢迎交流