这个是关于JDBC的小例子
主要是数据库的增删改查
分为MVC三层,通过VIEW层操作
但是不知道为什么只有add和delete的方法能完整显示出来,更新和查询只显示if(step==1)这一步程序就不能执行显示了,但是步骤都和add()方法的基本一样,不知道为什么不行/(ㄒoㄒ)/~~
是 step 定义的问题吗?? 想了很久也不能解决这个问题/(ㄒoㄒ)/~~
求各路英雄帮帮忙/(ㄒoㄒ)/~~
一下是源代码
项目结构----------------------------------------------------------------------
增加功能显示结果-------------------------------------------------------------------
更新功能显示结果-----------------------------------------------------------
查询功能显示结果---------------------------------------------------------------
以下是源代码(5个类)
View.java-----------------------------------------------
package view;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Scanner;
import action.GoddessAction;
import model.Goddess;
public class View {
private static final String CONTEXT="欢迎来到女神禁区：\n" +
"下面是女神禁区的功能列表：\n" +
"[MAIN/M]:主菜单\n" +
"[QUERY/Q]:查看全部女神的信息\n" +
"[GET/G]:查看某位女神的详细信息\n" +
"[ADD/A]:添加女神信息\n" +
"[UPDATE/U]:更新女神信息\n" +
"[DELETE/D]:删除女神信息\n" +
"[SEARCH/S]:查询女神信息(根据姓名、手机号来查询)\n" +
"[EXIT/E]:退出女神禁区\n" +
"[BREAK/B]:退出当前功能，返回主菜单";
private static final String OPERATION_MAIN="MAIN";
private static final String OPERATION_QUERY="QUERY";
private static final String OPERATION_GET="GET";
private static final String OPERATION_ADD="ADD";
private static final String OPERATION_UPDATE="UPDATE";
private static final String OPERATION_DELETE="DELETE";
private static final String OPERATION_SEARCH="SEARCH";
private static final String OPERATION_EXIT="EXIT";
private static final String OPERATION_BREAK="BREAK";
public static void main(String[] args) {
System.out.println(CONTEXT);
Scanner scan = new Scanner(System.in);
Goddess goddess = new Goddess();
Integer step = 1;
String prenious=null;
GoddessAction goddessAction = new GoddessAction();
while(scan.hasNext()){
String in = scan.next();
if(OPERATION_ADD.equals(in.toUpperCase())
||OPERATION_ADD.substring(0, 1).equals(in.toUpperCase())
||OPERATION_ADD.equals(prenious)){
prenious = OPERATION_ADD;
if(step == 1){
System.out.println("请输入你所要添加的女神[姓名]:");
}else if(step == 2){
goddess.setUser_name(in);
System.out.println("请输入你所要添加的女神[性别]:");
}else if(step == 3){
goddess.setSex(Integer.valueOf(in));
System.out.println("请输入你所要添加的女神[年龄]:");
}else if(step == 4){
goddess.setAge(Integer.valueOf(in));
System.out.println("请输入你所要添加的女神[生日]:");
}else if(step == 5){
SimpleDateFormat sf=new SimpleDateFormat("yyyy-MM-dd");
Date birthday=null;
try {
birthday = sf.parse(in);
goddess.setBirthday(birthday);
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("您输入的格式有错误,请重新输入");
step=5;
}
System.out.println("请输入你所要添加的女神[邮箱]:");
}else if(step == 6){
goddess.setEmail(in);
System.out.println("请输入你所要添加的女神[手机]:");
}else if(step == 7){
goddess.setMobile(in);
try {
goddessAction.add(goddess);
System.out.println("添加女神成功");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("新增女神失败");
}
}
if(OPERATION_ADD.equals(prenious)){
step++;
}
}else if(OPERATION_DELETE.equals(in.toUpperCase())
|| OPERATION_DELETE.substring(0, 1).equals(in.toUpperCase())){
try {
System.out.println("请输入您想要删除的女神");
String deleteIn = scan.next();
goddessAction.delete(Integer.valueOf(deleteIn));
System.out.println("成功删除所选女神!");
} catch (NumberFormatException e) {
// TODO Auto-generated catch block
System.out.println("删除女神失败!");
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}else if(OPERATION_UPDATE.equals(in.toUpperCase())
|| OPERATION_UPDATE.substring(0, 1).equals(in.toUpperCase())){
prenious = OPERATION_UPDATE;
if(step == 1){
System.out.println("请输入你所要更新的用户名");
}else if(step == 2){
goddess.setUser_name(in);
System.out.println("请输入你所要更新的年龄");
}else if(step == 3){
goddess.setAge(Integer.valueOf(in));
System.out.println("请输入你所要更新的生日");
}else if(step == 4){
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
Date birthday = null;
try {
birthday = sf.parse(in);
goddess.setBirthday(birthday);
System.out.println("请输入你所要更新的邮件");
} catch (ParseException e) {
// TODO Auto-generated catch block
System.out.println("您输入的日期格式错误!");
e.printStackTrace();
}
}else if(step == 5){
goddess.setEmail(in);
System.out.println("请输入你所要更新的手机号");
}else if(step == 6){
goddess.setMobile(in);
System.out.println("请输入你所要更新的ID");
}else if(step == 7){
try {
goddess.setId(Integer.valueOf(in));
goddessAction.update(goddess);
System.out.println("更新女神成功!");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(OPERATION_UPDATE.equals(prenious)){
step++;
}
} else if(OPERATION_QUERY.equals(in.toUpperCase()) ||
OPERATION_QUERY.substring(0, 1).equals(in.toUpperCase())){
List<Map<String,Object>> params = new ArrayList<Map<String,Object>>();
Map<String,Object> param = new HashMap<String, Object>();
if(step == 1){
System.out.println("请输入您要查询的信息的[列名]");
}else if(step == 2){
param.put("name", in);
System.out.println("请输入您要查询的信息的[关系]");
}else if(step == 3){
param.put("rel", in);
System.out.println("请输入您要查询的信息的[值]");
}else if(step == 4){
param.put("value", in);
}else if(step == 5){
try {
System.out.println("以下是我们为您所查询到的信息");
params.add(param);
goddessAction.queryFlex(params);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
step++;
}
}
}
}
GoddessAction.java-----------------------------------------------
package action;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import model.Goddess;
import dao.GodessDao;
package action;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import model.Goddess;
import dao.GodessDao;
public class GoddessAction {
GodessDao godessDao = new GodessDao();
public void add(Goddess goddess) throws SQLException{
godessDao.add(goddess);
}
public void delete(Integer id) throws SQLException{
godessDao.delete(id);
}
public void update(Goddess goddess) throws SQLException{
godessDao.update(goddess);
}
public List<Goddess> query() throws Exception{
return godessDao.query();
}
public List<Goddess> queryFlex(List<Map<String, Object>> params) throws Exception{
return godessDao.queryFlex(params);
}
}
GoddessDao.java-----------------------------------------------
package dao;
import java.sql.Date;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import db.DBUtil;
import model.Goddess;
/*
*数据库的增删改查
*/
public class GodessDao {
/*
* 增加
*/
public void add(Goddess goddess) throws SQLException{
//获得数据库连接
Connection conn = DBUtil.getConnection();
//编写sql语句
String sql = "" +
"insert into imooc_goddess" +
"(user_name,sex,age,birthday,email,mobile," +
"create_user,create_date,update_user,update_date,isdel)" +
"values("+
"?,?,?,?,?,?,'admin',current_date(),'admin',current_date(),1)";
//预编译
PreparedStatement preparedStatement = conn.prepareStatement(sql);
//传参赋值
preparedStatement.setString(1,goddess.getUser_name());
preparedStatement.setInt(2, goddess.getSex());
preparedStatement.setInt(3,goddess.getAge() );
preparedStatement.setDate(4, new Date(goddess.getBirthday().getTime()));
preparedStatement.setString(5,goddess.getEmail() );
preparedStatement.setString(6, goddess.getMobile());
/*preparedStatement.setString(7,goddess.getCreate_user() );
preparedStatement.setString(8,goddess.getUpdate_user());
preparedStatement.setInt(9, goddess.getIsdel());*/
//执行sql语句
preparedStatement.execute();
}
/*
* 删除
*/
public void delete(Integer id) throws SQLException{
Connection conn = DBUtil.getConnection();
String sql = "" +
" delete from imooc_goddess"+
" where id=? ";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setInt(1, id);
preparedStatement.execute();
}
/*
* 更新
*/
public void update(Goddess goddess) throws SQLException{
Connection conn= DBUtil.getConnection();
//记得sql语句前后加空格,不然报报错误,因为不加空格语句会成一行来执行
String sql = "" +
" update imooc_goddess "+
" set user_name=?,sex=1,age=?,birthday=?,email=?,mobile=?, "+
" update_user='admin',update_date=current_date(),isdel=1 "+
" where id=? ";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1, goddess.getUser_name());
/* preparedStatement.setInt(2, goddess.getSex());*/
preparedStatement.setInt(2, goddess.getAge());
preparedStatement.setDate(3, new Date(goddess.getBirthday().getTime()));
preparedStatement.setString(4, goddess.getEmail());
preparedStatement.setString(5, goddess.getMobile());
/*preparedStatement.setString(6, goddess.getUpdate_user());*/
/*preparedStatement.setInt(8, goddess.getIsdel())*/;
preparedStatement.setInt(6, goddess.getId());
preparedStatement.execute();
}
/*
* 查询全部
*/
public List<Goddess> query() throws Exception{
//获得数据库连接
Connection conn = DBUtil.getConnection();
Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery("select user_name,age from imooc_goddess");
List<Goddess> goddessesList = new ArrayList<Goddess>();
Goddess goddess = null;
while(resultSet.next()){
goddess=new Goddess();
goddess.setAge(resultSet.getInt("age"));
goddess.setUser_name(resultSet.getString("user_name"));
goddessesList.add(goddess);
}
return goddessesList;
}
/*
* 灵活查询
*/
public List<Goddess> queryFlex(List<Map<String,Object>> params) throws Exception{
List<Goddess> result = new ArrayList<Goddess>();
//获得数据库连接
Connection conn = DBUtil.getConnection();
StringBuilder sb = new StringBuilder();//单线程适用StringBuilder
sb.append(" select * from imooc_goddess where 1=1 ");//注意where1=1(永远为true)的好处,即使and后面的sql的语句,map取到的值为空,也可以执行这一句
if(params!=null && params.size()>0){
for(int i=0;i<params.size();i++){
Map<String, Object> map = params.get(i);
sb.append(" and " + map.get("name")+" " + map.get("rel") +" "+ map.get("value")+" ");
}
}
PreparedStatement preparedStatement = conn.prepareStatement(sb.toString());
System.out.println("SQL语句: "+sb.toString());
ResultSet resultSet = preparedStatement.executeQuery();
Goddess g = null;
while(resultSet.next()){
g=new Goddess();
g.setId( resultSet.getInt("id"));
g.setUser_name( resultSet.getString("user_name"));
g.setAge( resultSet.getInt("age"));
g.setSex( resultSet.getInt("sex"));
g.setBirthday( resultSet.getDate("birthday"));
g.setEmail( resultSet.getString("email"));
g.setMobile( resultSet.getString("mobile"));
g.setCreate_date( resultSet.getDate("create_date"));
g.setCreate_user( resultSet.getString("create_user"));
g.setUpdate_date( resultSet.getDate("update_date"));
g.setUpdate_user( resultSet.getString("update_user"));
g.setIsdel( resultSet.getInt("isdel"));
result.add(g);
}
return result;
}
/*
* 查询单个(根据ID)
* 注意:只有增删改操作才使用preparedStatement.execute()方法,查询操作不使用;
*/
public Goddess queryOne(Integer id) throws SQLException{
Goddess goddess = null;
Connection conn = DBUtil.getConnection();
String sql = " "+
" select * from imooc_goddess "+
" where id=? ";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setInt(1, id);
ResultSet resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
goddess = new Goddess();
goddess.setId(resultSet.getInt("id"));
goddess.setUser_name(resultSet.getString("user_name"));
goddess.setAge(resultSet.getInt("age"));
goddess.setSex(resultSet.getInt("sex"));
//java.sql.date可以自动转换为java.util.date; 因为java.util.Date 是 java.sql.Date 的父类
goddess.setBirthday(resultSet.getDate("birthday"));
goddess.setEmail(resultSet.getString("email"));
goddess.setMobile(resultSet.getString("mobile"));
goddess.setCreate_user(resultSet.getString("create_user"));
goddess.setCreate_date(resultSet.getDate("create_date"));
goddess.setUpdate_user(resultSet.getString("update_user"));
goddess.setUpdate_date(resultSet.getDate("update_date"));
goddess.setIsdel(resultSet.getInt("isdel"));
}
return goddess;
}
}
Goddess.java-----------------------------------------------
package model;
import java.util.Date;
public class Goddess {
private Integer id;
private String user_name;
private Integer sex;
private Integer age;
private Date birthday;
private String email;
private String mobile;
private String create_user;
private String update_user;
private Date create_date;
private Date update_date;
private Integer isdel;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUser_name() {
return user_name;
}
public void setUser_name(String user_name) {
this.user_name = user_name;
}
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 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 getCreate_date() {
return create_date;
}
public void setCreate_date(Date create_date) {
this.create_date = create_date;
}
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 +" , user_name=" + user_name + ", sex="
+ sex + ", age=" + age + ", birthday=" + birthday + ", email="
+ email + ", mobile=" + mobile + ", create_user=" + create_user
+ ", update_user=" + update_user + ", create_date="
+ create_date + ", update_date=" + update_date + ", isdel="
+ isdel + "]";
}
}
DBUtil.java-----------------------------------------------
package db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtil {
//一般情况下,本机地址都是127.0.0.1
private static final String URL="jdbc:mysql://127.0.0.1:3306/imooc_demo?useUnicode=true&characterEncoding=utf-8";
private static final String USER="root";
private static final String PASSWORD="123456";
private static Connection conn=null;
static {
try {
//1.加载驱动程序
Class.forName("com.mysql.jdbc.Driver");
//2.获得数据库连接
conn=DriverManager.getConnection(URL, USER, PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
return conn;
}
}
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Saya nak cakap dewi, nombor telefon awak telah bocor...
Adakah anda mempunyai teman lelaki?
Pada masa ini saya hanya melihat kelas pertama, View.java, dan terdapat sesuatu yang tidak kena dengan logik kod di dalamnya Setiap kali saya menggunakan pembolehubah langkah, nampaknya nilai awal tidak dipulihkan, bukan?
Boleh cuba
让对象编程hahaha