在java里对mysql进行增删改查
该操作比较简单。建四个类即可。
第一个类DBUtil
该类作用是建立java与mysql链接
private static final String MYSQL_URL = "jdbc:mysql://localhost:3306/###"; //连接到相应的数据库中
private static final String MYSQL_USERNAME = "####"; //输入用户名
private static final String MYSQL_PASSWORD = "####"; //输入密码
注意需要抛出异常。
package com.zzxtit.user.sys.common.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtil {
private static final String MYSQL_URL = "jdbc:mysql://localhost:3306/###";
private static final String MYSQL_USERNAME = "####";
private static final String MYSQL_PASSWORD = "####";
public static Connection getDBConnn() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
return DriverManager.getConnection(MYSQL_URL, MYSQL_USERNAME, MYSQL_PASSWORD);
}
public static void close(Connection conn) throws SQLException {
if(conn != null) {
conn.close();
}
}
public static void close(Statement stat) throws SQLException {
if(stat != null) {
stat.close();
}
}
public static void close(ResultSet rs) throws SQLException {
if(rs != null) {
rs.close();
}
}
}
第二个类UserInfo
定义属性
该步骤无难度
package com.zzxtit.user.sys;
import java.util.Date;
public class UserInfo {
private Integer userId;
private String username;
private String passwd;
private String real_name;
private int gender;
private String phone;
private String email;
private Date createtime;
private String salt;
private String avatar;
private String title;
private String title1;
private String title2;
private String title3;
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPasswd() {
return passwd;
}
public void setPasswd(String passwd) {
this.passwd = passwd;
}
public String getRealName() {
return real_name;
}
public void setRealName(String real_name) {
this.real_name = real_name;
}
public int getGender() {
return gender;
}
public void setGender(int gender) {
this.gender = gender;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getSalt() {
return salt;
}
public void setSalt(String salt) {
this.salt = salt;
}
public String getAvatar() {
return avatar;
}
public void setAvatar(String avatar) {
this.avatar = avatar;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getTitle1() {
return title1;
}
public void setTitle1(String title1) {
this.title1 = title1;
}
public String getTitle2() {
return title2;
}
public void setTitle2(String title2) {
this.title2 = title2;
}
public String getTitle3() {
return title3;
}
public void setTitle3(String title3) {
this.title3 = title3;
}
}
第三个类
接口类。。UserDao
package com.zzxtit.user.sys;
import java.util.List;
public interface UserDao {
public void insertUserInfo(UserInfo user);
public void insertUserInfo1(UserInfo user);
public void deleteUserInfo(Integer userId);
public void updateUserInfo(UserInfo user);
public UserInfo getUserInfoById(Integer userId);
public List<UserInfo> getUserInfoByAttr();
public List<UserInfo> getUserInfoByAttr1();
}
第四个类。
难点来了。增删改查。。
在这里面有几点需要注意。
1.StringBuffer sql = new StringBuffer( "insert into title10(username,real_name,phone,email,gender,createtime,passwd) values(");
sql.append("'").append(user.getUsername()).append("','").append(user.getRealName()).append("','")
.append(user.getPhone()).append("','").append(user.getEmail()).append("','").append(user.getGender()).append("',now()").append(",'").append(user.getPasswd()).append("')") ;
System.out.println(sql.toString());
Connection conn = null;
Statement stat = null;
这里面 StringBuffer sql = new StringBuffer( "insert into title10(username,real_name,phone,email,gender,createtime,passwd) values(");
此步骤是手动操作在java里面对mysql进行增删改查。其中的sql语句可复制粘贴到mysql中运行。
分别对应mysql中的增删改查。
增加。
@Override
public void insertUserInfo(UserInfo user) {
StringBuffer sql = new StringBuffer( "insert into title10(username,real_name,phone,email,gender,createtime,passwd) values(");
sql.append("'").append(user.getUsername()).append("','").append(user.getRealName()).append("','")
.append(user.getPhone()).append("','").append(user.getEmail()).append("','").append(user.getGender()).append("',now()").append(",'").append(user.getPasswd()).append("')") ;
System.out.println(sql.toString());
Connection conn = null;
Statement stat = null;
try {
conn = DBUtil.getDBConnn();
stat = conn.createStatement();
stat.executeUpdate(sql.toString());
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
DBUtil.close(stat);
DBUtil.close(conn);
}catch(Exception e) {
}
}
}
删除
@Override
public void deleteUserInfo( Integer userId) {
String sql = "delete from title10 where userId = '"+userId+"'";
Connection conn = null;
Statement stat = null;
try {
conn = DBUtil.getDBConnn();
stat = conn.createStatement();
// stat.setString(1,userId);
stat.executeUpdate(sql.toString());
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
DBUtil.close(stat);
DBUtil.close(conn);
}catch(Exception e) {
}
}
}
修改
@Override
public void updateUserInfo(UserInfo user) {
String sql = "update title10 set username='"+user.getUsername()+"',real_name='"+user.getRealName()+"' where userId='"+user.getUserId()+"'";
Connection conn = null;
Statement stat = null;
System.out.println(sql);
try {
conn = DBUtil.getDBConnn();
stat = conn.createStatement();
stat.executeUpdate(sql.toString());
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
DBUtil.close(stat);
DBUtil.close(conn);
}catch(Exception e) {
}
}
}
查询所有
public List<UserInfo> getUserInfoByAttr1() {
List<UserInfo> userList = new ArrayList<UserInfo>();
String sql = "select title.* , title1 , title2, title3\r\n" +
"from title, title1 , title2 ,title3\r\n" +
"where title.id = title1.id and title.id=title2.id and title.id=title3.id";
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
UserInfo user = null;
try {
conn = DBUtil.getDBConnn();
stat = conn.createStatement();
rs = stat.executeQuery(sql);
while(rs.next()) {
user = new UserInfo();
user.setTitle(rs.getString("title"));
user.setTitle1(rs.getString("title1"));
user.setTitle2(rs.getString("title2"));
user.setTitle3(rs.getString("title3"));
userList.add(user);
}
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
DBUtil.close(rs);
DBUtil.close(conn);
}catch(Exception e) {
}
}
return userList;
}
根据ID查询
@Override
public UserInfo getUserInfoById(Integer userId) {
List<UserInfo> userList = new ArrayList<UserInfo>();
String sql = "select * from title10 where userId = '"+userId+"'";
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
UserInfo user = null;
try {
conn = DBUtil.getDBConnn();
stat = conn.createStatement();
rs = stat.executeQuery(sql);
while(rs.next()) {
user = new UserInfo();
user.setUsername(rs.getString("username"));
user.setPasswd(rs.getString("passwd"));
user.setGender(rs.getInt("gender"));
user.setPhone(rs.getString("phone"));
user.setRealName(rs.getString("real_name"));
user.setEmail(rs.getString("email"));
user.setCreatetime(rs.getDate("createtime"));
userList.add(user);
}
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
DBUtil.close(rs);
DBUtil.close(conn);
}catch(Exception e) {
}
}
return null;
}
}
package com.zzxtit.user.sys;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.zzxtit.user.sys.common.util.DBUtil;
public class UserDaoImpl implements UserDao {
@Override
public void insertUserInfo(UserInfo user) {
StringBuffer sql = new StringBuffer( "insert into title10(username,real_name,phone,email,gender,createtime,passwd) values(");
sql.append("'").append(user.getUsername()).append("','").append(user.getRealName()).append("','")
.append(user.getPhone()).append("','").append(user.getEmail()).append("','").append(user.getGender()).append("',now()").append(",'").append(user.getPasswd()).append("')") ;
System.out.println(sql.toString());
Connection conn = null;
Statement stat = null;
try {
conn = DBUtil.getDBConnn();
stat = conn.createStatement();
stat.executeUpdate(sql.toString());
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
DBUtil.close(stat);
DBUtil.close(conn);
}catch(Exception e) {
}
}
}
public void insertUserInfo1(UserInfo user) {
StringBuffer sql = new StringBuffer( "insert into title11(username,passwd) values(");
sql.append("'").append(user.getUsername()).append("','").append(user.getPasswd()).append("')") ;
System.out.println(sql.toString());
Connection conn = null;
Statement stat = null;
try {
conn = DBUtil.getDBConnn();
stat = conn.createStatement();
stat.executeUpdate(sql.toString());
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
DBUtil.close(stat);
DBUtil.close(conn);
}catch(Exception e) {
}
}
}
@Override
public void deleteUserInfo( Integer userId) {
String sql = "delete from title10 where userId = '"+userId+"'";
Connection conn = null;
Statement stat = null;
try {
conn = DBUtil.getDBConnn();
stat = conn.createStatement();
// stat.setString(1,userId);
stat.executeUpdate(sql.toString());
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
DBUtil.close(stat);
DBUtil.close(conn);
}catch(Exception e) {
}
}
}
@Override
public void updateUserInfo(UserInfo user) {
String sql = "update title10 set username='"+user.getUsername()+"',real_name='"+user.getRealName()+"' where userId='"+user.getUserId()+"'";
Connection conn = null;
Statement stat = null;
System.out.println(sql);
try {
conn = DBUtil.getDBConnn();
stat = conn.createStatement();
stat.executeUpdate(sql.toString());
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
DBUtil.close(stat);
DBUtil.close(conn);
}catch(Exception e) {
}
}
}
/* @Override
public UserInfo getUserInfoById(Integer userId) {
return null;
}
*/
@Override
public List<UserInfo> getUserInfoByAttr() {
List<UserInfo> userList = new ArrayList<UserInfo>();
String sql = "select * from title10";
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
UserInfo user = null;
try {
conn = DBUtil.getDBConnn();
stat = conn.createStatement();
rs = stat.executeQuery(sql);
while(rs.next()) {
user = new UserInfo();
user.setUsername(rs.getString("username"));
user.setPasswd(rs.getString("passwd"));
user.setGender(rs.getInt("gender"));
user.setPhone(rs.getString("phone"));
user.setRealName(rs.getString("real_name"));
user.setEmail(rs.getString("email"));
user.setCreatetime(rs.getDate("createtime"));
userList.add(user);
}
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
DBUtil.close(rs);
DBUtil.close(conn);
}catch(Exception e) {
}
}
return userList;
}
public List<UserInfo> getUserInfoByAttr1() {
List<UserInfo> userList = new ArrayList<UserInfo>();
String sql = "select title.* , title1 , title2, title3\r\n" +
"from title, title1 , title2 ,title3\r\n" +
"where title.id = title1.id and title.id=title2.id and title.id=title3.id";
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
UserInfo user = null;
try {
conn = DBUtil.getDBConnn();
stat = conn.createStatement();
rs = stat.executeQuery(sql);
while(rs.next()) {
user = new UserInfo();
user.setTitle(rs.getString("title"));
user.setTitle1(rs.getString("title1"));
user.setTitle2(rs.getString("title2"));
user.setTitle3(rs.getString("title3"));
userList.add(user);
}
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
DBUtil.close(rs);
DBUtil.close(conn);
}catch(Exception e) {
}
}
return userList;
}
@Override
public UserInfo getUserInfoById(Integer userId) {
List<UserInfo> userList = new ArrayList<UserInfo>();
String sql = "select * from title10 where userId = '"+userId+"'";
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
UserInfo user = null;
try {
conn = DBUtil.getDBConnn();
stat = conn.createStatement();
rs = stat.executeQuery(sql);
while(rs.next()) {
user = new UserInfo();
user.setUsername(rs.getString("username"));
user.setPasswd(rs.getString("passwd"));
user.setGender(rs.getInt("gender"));
user.setPhone(rs.getString("phone"));
user.setRealName(rs.getString("real_name"));
user.setEmail(rs.getString("email"));
user.setCreatetime(rs.getDate("createtime"));
userList.add(user);
}
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
DBUtil.close(rs);
DBUtil.close(conn);
}catch(Exception e) {
}
}
return null;
}
}