数据库表结构:
CREATE TABLE `goddess` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(30) NOT NULL,
`sex` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`birthday` date DEFAULT NULL,
`email` varchar(30) DEFAULT NULL,
`mobile` varchar(11) DEFAULT NULL,
`create_user` varchar(30) DEFAULT NULL,
`create_date` date DEFAULT NULL,
`update_user` varchar(30) DEFAULT NULL,
`update_date` date DEFAULT NULL,
`isdel` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
数据库表数据:
insert into `goddess`(`id`,`user_name`,`sex`,`age`,`birthday`,`email`,`mobile`,`create_user`,`create_date`,`update_user`,`update_date`,`isdel`) values
(1,'小溪',1,22,'2000-12-12','xiaoxi@163.com','13911111111','ADMIN','2015-01-08','ADMIN','2015-01-08',0),
(2,'小夏',1,19,'2000-12-12','xiaoxi@163.com','13911111111','ADMIN','2015-01-08','admin','2016-11-22',0),
(3,'小凌',1,22,'2000-12-12','xiaoxi@163.com','13911111111','ADMIN','2015-01-08','ADMIN','2015-01-08',0),
(4,'小红',1,18,'2016-11-21','13838@qq.com','13838383888','admin','2016-11-21','admin','2016-11-21',0),
(5,'小小',1,20,'1990-02-02','123456@qq.com','12345678543','admin','2016-11-21','admin','2016-11-21',0),
(6,'小惜',1,22,'1994-03-03','123@qq.com','13512345678','admin','2016-11-21','admin','2016-11-21',0),
(7,'小小',1,25,'1991-03-06','1991@qq.com','13812345678','admin','2016-11-22','admin','2016-11-22',0);
/**
* JDBC工具类
* @author:凌_惜
* @date:2016年11月21日 上午9:48:28
*/
public class JDBCUtil {
private static final String URL = "jdbc:mysql://localhost:3306/test";
private static final String USER = "root";
private static final String PASSWORD = "1234";
private static Connection conn = null;
static {
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取数据库连接
* @return
*/
public static Connection getConnection () {
return conn;
}
}
/**
* 女神实体类
* @author:凌_惜
* @date:2016年11月21日 上午10:20:29
*/
public class Goddess {
private Integer id;
private String userName; //姓名
private Integer sex; //性别
private Integer age; //年龄
private Date birthday; //生日
private String email; //邮箱
private String mobile; //手机号
private String createUser; //创建人
private Date createDate; //创建时间
private String updateUser; //更新人
private Date updateDate; //更新时间
private Integer isdel; //删除标记 0:否 1:是
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 getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
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 getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
public String getCreateUser() {
return createUser;
}
public void setCreateUser(String createUser) {
this.createUser = createUser;
}
public Date getCreateDate() {
return createDate;
}
public void setCreateDate(Date createDate) {
this.createDate = createDate;
}
public String getUpdateUser() {
return updateUser;
}
public void setUpdateUser(String updateUser) {
this.updateUser = updateUser;
}
public Date getUpdateDate() {
return updateDate;
}
public void setUpdateDate(Date updateDate) {
this.updateDate = updateDate;
}
public Integer getIsdel() {
return isdel;
}
public void setIsdel(Integer isdel) {
this.isdel = isdel;
}
}
/**
* 女神Dao层
* @author:凌_惜
* @date:2016年11月21日 上午10:30:55
*/
public class GoddessDao {
/**
* 添加女神信息
* @throws Exception
*/
public void addGoddess (Goddess goddess) throws Exception {
Connection conn = JDBCUtil.getConnection();
String sql = "insert into goddess(user_name,sex,age,birthday,email,mobile,"
+ "create_user,create_date,update_user,update_date,isdel) "
+ "values(?,?,?,?,?,?,?,current_date(),?,current_date(),?)";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setString(1, goddess.getUserName());
statement.setInt(2, goddess.getSex());
statement.setInt(3, goddess.getAge());
statement.setDate(4, new Date(goddess.getBirthday().getTime()));
statement.setString(5, goddess.getEmail());
statement.setString(6, goddess.getMobile());
statement.setString(7, goddess.getCreateUser());
statement.setString(8, goddess.getUpdateUser());
statement.setInt(9, goddess.getIsdel());
statement.execute();
}
/**
* 批量删除女神信息(逻辑删除)
* @throws Exception
*/
public void delGoddess (Integer[] ids) throws Exception {
Connection conn = JDBCUtil.getConnection();
String sql = "update goddess set isdel=1 where id=?";
PreparedStatement statement = conn.prepareStatement(sql);
for(int i =0 ;i<ids.length;i++){
statement.setInt(1,ids[i]);
statement.addBatch();
}
statement.executeBatch();
}
/**
* 更改女神信息
* @throws Exception
*/
public void updateGoddess (Goddess goddess) throws Exception {
Connection conn = JDBCUtil.getConnection();
String sql = "update goddess set user_name=?,age=?,birthday=?,email=?,mobile=?,"
+ "update_user=?,update_date=current_date() where id=?";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setString(1, goddess.getUserName());
statement.setInt(2, goddess.getAge());
statement.setDate(3, new Date(goddess.getBirthday().getTime()));
statement.setString(4, goddess.getEmail());
statement.setString(5, goddess.getMobile());
statement.setString(6, goddess.getUpdateUser());
statement.setInt(7, goddess.getId());
statement.execute();
}
/**
* 查询全部女神信息
* @return
* @throws Exception
*/
public List<Goddess> queryGoddess () throws Exception {
Connection conn = JDBCUtil.getConnection();
String sql = "select * from goddess where isdel=0";
PreparedStatement statement = conn.prepareStatement(sql);
ResultSet result = statement.executeQuery();
List<Goddess> goddessList = new ArrayList<Goddess>();
while (result.next()) {
Goddess goddess = new Goddess();
goddess.setId(result.getInt("id"));
goddess.setUserName(result.getString("user_name"));
goddessList.add(goddess);
}
return goddessList;
}
/**
* 根据姓名模糊查询女神信息
* @param name
* @return
*/
public List<Goddess> queryGoddessByUserName (String name) throws Exception {
Connection conn = JDBCUtil.getConnection();
String sql = "select * from goddess where isdel=0 and user_name like ?";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setString(1, "%" + name + "%");
ResultSet result = statement.executeQuery();
List<Goddess> goddessList = new ArrayList<Goddess>();
while (result.next()) {
Goddess goddess = new Goddess();
goddess.setId(result.getInt("id"));
goddess.setUserName(result.getString("user_name"));
goddessList.add(goddess);
}
return goddessList;
}
/**
* 根据id查询女神信息
* @return
* @throws Exception
*/
public Goddess getGoddess (Integer id) throws Exception {
Connection conn = JDBCUtil.getConnection();
String sql = "select * from goddess where isdel=0 and id=?";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setInt(1, id);
ResultSet result = statement.executeQuery();
Goddess goddess = null;
while (result.next()) {
goddess = new Goddess();
goddess.setId(result.getInt("id"));
goddess.setUserName(result.getString("user_name"));
goddess.setSex(result.getInt("sex"));
goddess.setAge(result.getInt("age"));
goddess.setBirthday(result.getDate("birthday"));
goddess.setEmail(result.getString("email"));
goddess.setMobile(result.getString("mobile"));
}
return goddess;
}
}
/**
* 女神action层
* @author:凌_惜
* @date:2016年11月21日 下午1:13:21
*/
public class GoddessAction {
/**
* 添加
* @param goddess
*/
public void add (Goddess goddess) throws Exception {
GoddessDao dao = new GoddessDao();
goddess.setSex(1);
goddess.setCreateUser("admin");
goddess.setUpdateUser("admin");
goddess.setIsdel(0);
dao.addGoddess(goddess);
}
/**
* 删除
* @param id
*/
public void delete (Integer[] ids) throws Exception {
GoddessDao dao = new GoddessDao();
dao.delGoddess(ids);
}
/**
* 更新
* @param goddess
*/
public void update (Goddess goddess) throws Exception {
GoddessDao dao = new GoddessDao();
goddess.setUpdateUser("admin");
dao.updateGoddess(goddess);
}
/**
* 查询全部
* @return
*/
public List<Goddess> query () throws Exception {
GoddessDao dao = new GoddessDao();
List<Goddess> list = dao.queryGoddess();
return list;
}
/**
* 根据名字模糊查询
* @param name
* @return
*/
public List<Goddess> queryByUsername (String name) throws Exception {
GoddessDao dao = new GoddessDao();
return dao.queryGoddessByUserName(name);
}
/**
* 根据id查询
* @param id
* @return
*/
public Goddess getGoddess (Integer id) throws Exception {
GoddessDao dao = new GoddessDao();
return dao.getGoddess(id);
}
}
/**
* 女神view层
* @author:凌_惜
* @date:2016年11月21日 下午2:52:48
*/
public class GodessView {
private static final String CONTEXT = "=====欢迎来到女神禁区,下面是女神禁区功能列表=====\n"
+ "字母[m]:主菜单\n"
+ "字母[q]:查看全部女神信息\n"
+ "字母[g]:查看某位女神详细信息\n"
+ "字母[a]:添加女神信息\n"
+ "字母[u]:更新女神信息\n"
+ "字母[d]:删除女神信息\n"
+ "字母[s]:根据名字模糊查询女神信息\n"
+ "字母[b]:退出当前功能,返回主菜单\n"
+ "字母[e]:退出女神禁区\n"
+ "请选择:";
private static final String OPERATION_MAIN = "m";
private static final String OPERATION_QUERY = "q";
private static final String OPERATION_GET = "g";
private static final String OPERATION_ADD = "a";
private static final String OPERATION_UPDATE = "u";
private static final String OPERATION_DELETE = "d";
private static final String OPERATION_SEARCH = "s";
private static final String OPERATION_BREAK = "b";
private static final String OPERATION_EXIT = "e";
/**
* 程序入口
* @param args
*/
public static void main(String[] args) {
System.out.println(CONTEXT);
Scanner scan = new Scanner(System.in);
GoddessAction goddessAction = new GoddessAction();
Goddess goddess = null;
List<Goddess> list = null;
String pervious = null;
Integer step = 1;
while (scan.hasNext()) {
String flag = scan.next().trim();
//退出
if (OPERATION_EXIT.equalsIgnoreCase(flag)) {
System.out.println("你已成功退出女神禁区!");
break;
}
//主菜单
if (OPERATION_MAIN.equalsIgnoreCase(flag)) {
step = 1;
pervious = null;
System.out.println(CONTEXT);
}
//退出当前功能,返回主菜单
if (OPERATION_BREAK.equalsIgnoreCase(flag)) {
step = 1;
pervious = null;
System.out.println(CONTEXT);
}
//查看全部女神信息
if (OPERATION_QUERY.equalsIgnoreCase(flag)) {
try {
list = goddessAction.query();
if (list != null) {
System.out.println("---本禁区全部女神基本信息如下---");
for (Goddess g : list) {
System.out.println("编号:" + g.getId() + ",姓名:" + g.getUserName());
}
} else {
System.out.println("查询的女神不存在!");
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("查询女神基本信息失败!");
}
}
//查看某个女神详细信息
if (OPERATION_GET.equalsIgnoreCase(flag) || OPERATION_GET.equals(pervious)) {
pervious = OPERATION_GET;
if (step == 1) {
System.out.println("请输入要查看的女神[ID]:");
} else if (step == 2) {
try {
goddess = goddessAction.getGoddess(Integer.valueOf(flag));
if (goddess != null) {
step = 1;
pervious = null;
System.out.println("编号:" + goddess.getId() + "\n姓名:" + goddess.getUserName()
+ "\n性别:" + (goddess.getSex() == 1 ? "女":"男") + "\n年龄:" + goddess.getAge()
+ "\n生日:" + goddess.getBirthday() + "\n邮箱:" + goddess.getEmail()
+ "\n手机号:" + goddess.getMobile());
} else {
step = 1;
pervious = null;
System.out.println("查询的女神不存在!");
}
} catch (NumberFormatException e) {
step = 1;
pervious = null;
System.out.println("请输入正确的女神ID:");
} catch (Exception e) {
e.printStackTrace();
step = 1;
pervious = null;
System.out.println("查询女神详细信息失败!");
}
}
if(OPERATION_GET.equals(pervious)){
step ++;
}
}
//根据名字模糊查询女神信息
if (OPERATION_SEARCH.equalsIgnoreCase(flag) || OPERATION_SEARCH.equals(pervious)) {
pervious = OPERATION_SEARCH;
if (step == 1) {
System.out.println("请输入任意女神名字的一个或多个字:");
} else if (step == 2) {
try {
list = goddessAction.queryByUsername(flag);
if (list != null) {
for (Goddess g : list) {
System.out.println("编号:" + g.getId() + ",姓名:" + g.getUserName());
}
step = 1;
pervious = null;
} else {
step = 1;
pervious = null;
System.out.println("查询的女神不存在!");
}
} catch (Exception e) {
e.printStackTrace();
step = 1;
pervious = null;
System.out.println("查询女神信息失败!");
}
}
if(OPERATION_SEARCH.equals(pervious)){
step ++;
}
}
//添加女神信息
if (OPERATION_ADD.equalsIgnoreCase(flag) || OPERATION_ADD.equals(pervious)) {
pervious = OPERATION_ADD;
if (step == 1) {
System.out.println("请输入女神[姓名]:");
} else if (step == 2) {
goddess = new Goddess();
goddess.setUserName(flag);
System.out.println("请输入女神[年龄]:");
} else if (step == 3) {
try {
goddess.setAge(Integer.valueOf(flag));
System.out.println("请输入女神[生日],格式为yyyy-MM-dd:");
} catch (NumberFormatException e) {
step = 2;
System.out.println("请输入正确的女神的[年龄]:");
}
} else if (step == 4) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
goddess.setBirthday(sdf.parse(flag));
} catch (ParseException e) {
step = 3;
System.out.println("请输入正确的女神的[生日]:");
}
System.out.println("请输入女神[邮箱]:");
} else if (step == 5) {
goddess.setEmail(flag);
System.out.println("请输入女神[手机号]:");
} else if (step == 6) {
goddess.setMobile(flag);
try {
goddessAction.add(goddess);
System.out.println("添加女神信息成功!");
step = 1;
pervious = null;
} catch (Exception e) {
e.printStackTrace();
step = 1;
pervious = null;
System.out.println("添加女神信息失败!");
}
}
if (OPERATION_ADD.equals(pervious)) {
step ++;
}
}
//更新女神信息
if (OPERATION_UPDATE.equalsIgnoreCase(flag) || OPERATION_UPDATE.equals(pervious)) {
pervious = OPERATION_UPDATE;
if (step == 1) {
System.out.println("请输入要更新的女神的[ID]:");
} else if (step == 2) {
try {
Integer id = Integer.valueOf(flag);
goddess = goddessAction.getGoddess(id);
if (goddess == null) {
step = 1;
System.out.println("输入的女神[ID]不存在哦!");
}
} catch (NumberFormatException e) {
step = 1;
System.out.println("请输入正确的女神[ID]]:");
} catch (Exception e) {
step = 1;
System.out.println("输入的女神[ID]不存在哦!");
}
System.out.println("请输入女神[姓名]:(如不更新该字段,则输入null)");
} else if (step == 3) {
if (!"null".equalsIgnoreCase(flag)) {
goddess.setUserName(flag);
}
System.out.println("请输入女神[年龄]:(如不更新该字段,则输入null)");
} else if (step == 4) {
try {
if (!"null".equalsIgnoreCase(flag)) {
Integer age = Integer.valueOf(flag);
goddess.setAge(age);
}
System.out.println("请输入女神[生日],格式为yyyy-MM-dd:(如不更新该字段,则输入null)");
} catch (NumberFormatException e) {
step = 3;
System.out.println("请输入正确的女神[年龄]:");
}
} else if (step == 5) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
if (!"null".equalsIgnoreCase(flag)) {
goddess.setBirthday(sdf.parse(flag));
}
System.out.println("请输入女神[邮箱]:(如不更新该字段,则输入null)");
} catch (ParseException e) {
step = 4;
System.out.println("请输入正确的女神的[生日]:");
}
} else if (step == 6) {
if (!"null".equalsIgnoreCase(flag)) {
goddess.setEmail(flag);
}
System.out.println("请输入女神[手机号]:(如不更新该字段,则输入null)");
} else if (step == 7) {
if (!"null".equalsIgnoreCase(flag)) {
goddess.setMobile(flag);
}
try {
goddessAction.update(goddess);
System.out.println("更新女神信息成功!");
step = 1;
pervious = null;
} catch (Exception e) {
e.printStackTrace();
step = 1;
pervious = null;
System.out.println("更新女神信息失败!");
}
}
if (OPERATION_UPDATE.equals(pervious)) {
step ++;
}
}
//删除女神信息
if (OPERATION_DELETE.equalsIgnoreCase(flag) || OPERATION_DELETE.equals(pervious)) {
pervious = OPERATION_DELETE;
if (step == 1) {
System.out.println("请输入要删除的女神[ID]:(删除多个用逗号隔开,如1,2)");
} else if (step == 2) {
try {
String[] ss = flag.split(",");
Integer[] ids = new Integer[ss.length];
for (int i = 0; i < ss.length; i++) {
ids[i] = Integer.parseInt(ss[i]);
}
goddessAction.delete(ids);
System.out.println("删除女神信息成功!");
step = 1;
pervious = null;
} catch (NumberFormatException e) {
step = 1;
System.out.println("请输入正确的女神[ID]:");
} catch (Exception e) {
e.printStackTrace();
step = 1;
pervious = null;
System.out.println("删除女神信息失败!");
}
}
if(OPERATION_DELETE.equals(pervious)){
step ++;
}
}
}
}
}
打开App,阅读手记
热门评论
数据库的代码或者视图也弄出来啊
不然谁知道数据库里面的是什么
数据库的代码或者视图也弄出来啊
不然谁知道数据库里面的是什么
更新那里有点问题吧,如果输入null会出错的