本文采用的mvc模型
1.模型层
package com.suse.model;
import java.util.Date;
public class Goddess {
private Integer id;
private String username;
private Integer age;
private Integer sex;
private Date birthday;
private String email;
private String mobil;
private Date create_date;
private String create_user;
private String update_user;
private Date update_date;
private Integer isdel;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Integer getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getMobil() {
return mobil;
}
public void setMobil(String mobil) {
this.mobil = mobil;
}
public Date getCreate_date() {
return create_date;
}
public void setCreate_date(Date create_date) {
this.create_date = create_date;
}
public String getCreate_user() {
return create_user;
}
public void setCreate_user(String create_user) {
this.create_user = create_user;
}
public String getUpdate_user() {
return update_user;
}
public void setUpdate_user(String update_user) {
this.update_user = update_user;
}
public Date getUpdate_date() {
return update_date;
}
public void setUpdate_date(Date update_date) {
this.update_date = update_date;
}
public Integer getIsdel() {
return isdel;
}
public void setIsdel(Integer isdel) {
this.isdel = isdel;
}
@Override
public String toString() {
return "Goddess [id=" + id + ", username=" + username + ", age=" + age
+ ", sex=" + sex + ", birthday=" + birthday + ", email="
+ email + ", mobil=" + mobil + ", create_date=" + create_date
+ ", create_user=" + create_user + ", update_user="
+ update_user + ", update_date=" + update_date + ", isdel="
+ isdel + "]";
}
}
2.业务逻辑层
package com.suse.dao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.suse.db.DButil;
import com.suse.model.Goddess;
public class GoddessDao {
// 增加元素
public void addGoddess(Goddess g) throws SQLException {
Connection conn = DButil.getConnection();// 获得mysql数据库的连接
String sql = "" + "insert into imooc"
+ "(id,username,sex,age,birthday,email,mobil,"
+ "create_user,create_date,update_user,update_date,isdel)"
+ "values("
+ "?,?,?,?,?,?,?,?,current_date(),?,current_date(),?)";
PreparedStatement ptmt = conn.prepareStatement(sql);
ptmt.setInt(1, g.getId());
ptmt.setString(2, g.getUsername());
ptmt.setInt(3, g.getSex());
ptmt.setInt(4, g.getAge());
ptmt.setDate(5, new Date(g.getBirthday().getTime()));
ptmt.setString(6, g.getEmail());
ptmt.setString(7, g.getMobil());
ptmt.setString(8, g.getCreate_user());
ptmt.setString(9, g.getUpdate_user());
ptmt.setInt(10, g.getIsdel());
ptmt.execute();
}
// 更新元素
public void updateGoddess(Goddess g) throws SQLException {
Connection conn = DButil.getConnection();// 获得mysql数据库的连接
String sql = "" + "update imooc"
+ " set username=?,sex=?,age=?,birthday=?,email=?,mobil=?, "
+ "update_user=?,update_date=current_date(),isdel=? "
+ " where id=?";
PreparedStatement ptmt = conn.prepareStatement(sql);
ptmt.setString(1, g.getUsername());
ptmt.setInt(2, g.getSex());
ptmt.setInt(3, g.getAge());
ptmt.setDate(4, new Date(g.getBirthday().getTime()));
ptmt.setString(5, g.getEmail());
ptmt.setString(6, g.getMobil());
ptmt.setString(7, g.getUpdate_user());
ptmt.setInt(8, g.getIsdel());
ptmt.setInt(9, g.getId());
ptmt.execute();
}
// 删除元素
public void delGoddess(Integer id) throws SQLException {
Connection conn = DButil.getConnection();// 获得mysql数据库的连接
String sql = "" + "delete FROM imooc" + " where id=?";
PreparedStatement ptmt = conn.prepareStatement(sql);
ptmt.setInt(1, id);
ptmt.execute();
}
// 多值查询
public List<Goddess> query() throws SQLException {
Connection conn = DButil.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select username,age from imooc");// 查询executeQuery
List<Goddess> gs = new ArrayList<Goddess>();// 返回多个数据
Goddess g = null;
while (rs.next()) {
g = new Goddess();
g.setUsername(rs.getString("username"));
g.setAge(rs.getInt("age"));
gs.add(g);
}
return gs;
}
// 单个元组的所有信息查询
public Goddess get(Integer id) throws SQLException {
Connection conn = DButil.getConnection();
String sql = "" + " select * from imooc" + " where id=?";
PreparedStatement ptmt = conn.prepareStatement(sql);
ptmt.setInt(1, id);
ResultSet rs = ptmt.executeQuery();
Goddess gs = new Goddess();
while (rs.next()) {
gs.setId(rs.getInt("id"));
gs.setUsername(rs.getString("username"));
gs.setAge(rs.getInt("age"));
gs.setSex(rs.getInt("sex"));
gs.setBirthday(rs.getDate("birthday"));
gs.setEmail(rs.getString("email"));
gs.setMobil(rs.getString("mobil"));
gs.setCreate_date(rs.getDate("create_date"));
gs.setCreate_user(rs.getString("create_user"));
gs.setUpdate_date(rs.getDate("update_date"));
gs.setUpdate_user(rs.getString("update_user"));
gs.setIsdel(rs.getInt("isdel"));
}
return gs;
}
}
3.控制层
package com.suse.action;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import com.suse.dao.GoddessDao;
import com.suse.model.Goddess;
public class GoddessAction {
public static void main(String[] args) throws SQLException {
GoddessDao g = new GoddessDao();
// 调用查询方法
/*
* 查询测试
*
*
*
* List<Goddess> gs = g.query(); for (Goddess goddess : gs) {// 遍历所获数据
* System.out.println(goddess.getUsername() + "," + goddess.getAge());
*
* }
*/
Goddess gl = new Goddess();
/*
* 插入数据测试
*/
gl.setId(6);
gl.setUsername("小美");
gl.setAge(20);
gl.setSex(1);
gl.setBirthday(new Date());
gl.setEmail("xiaomei@suse.com");
gl.setMobil("123456789");
gl.setCreate_user("admin");
gl.setUpdate_user("admin");
gl.setIsdel(1);
// 调用插入方法
g.addGoddess(gl);
gl.setUsername("小美");
gl.setAge(20);
gl.setSex(1);
gl.setBirthday(new Date());
gl.setEmail("xiaomei@suse.com");
gl.setMobil("18882020596");
gl.setUpdate_user("admin");
gl.setIsdel(1);
gl.setId(3);
//调用更新方法
g.updateGoddess(gl);
//调用删除方法
// g.delGoddess(3);
//调用查询单个元组的所有信息方法
Goddess g2 = g.get(6);
System.out.println(g2.toString());
}
}
Jdbc工具类:
package com.suse.db;
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 URL = "jdbc:mysql://localhost/imooc?useUnicode=true&characterEncoding=utf-8";// 解决中文乱码问题
private static final String USER = "root";
private static final String PASSWORD = "数据库密码";
private static Connection conn = null;
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
// 获得数据库的连接
try {
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() {
return conn;
}
/*
* public static void main(String[] args) throws Exception { // 1.加载驱动程序
*
* Class.forName("com.mysql.jdbc.Driver"); // 获得数据库的连接 Connection conn =
* DriverManager.getConnection(URL, USER, PASSWORD); // 3通过数据库的连接,实现增删改查
* Statement stmt = conn.createStatement(); ResultSet rs =
* stmt.executeQuery("select username,age from imooc");// 查询executeQuery
* while (rs.next()) { System.out.println(rs.getString("username") +
* rs.getInt("age"));
*
* }
*/
// }
}