(本项目为作者自己初学所写,部分存在bug,仅供参考...)
利用数据库与JAVA的连接实现对数据的操作,该项目使用Eclipse 和 MySQL现,使用MVC作为框架。
要求如下:
1:对线路的增、删、改、查
2:创建用户名、密码实现购票
3:用户可进行修改目的地
4:只有进入内部系统才能查看用户的所有信息
在MySQL中创建库,表:
库:subway ---- 表:tickets 和 users
+----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+----------------+
| id | tinyint(4) | NO | PRI | NULL | auto_increment |
| terminal | varchar(40) | NO | | NULL | |
| price | int(10) unsigned | NO | | NULL | |
| surplus | tinyint(3) unsigned | NO | | NULL | |
+----------+---------------------+------+-----+---------+----------------+
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | tinyint(4) | NO | PRI | NULL | auto_increment |
| client | varchar(30) | NO | | NULL | |
| password | varchar(10) | NO | | NULL | |
| terminal | varchar(40) | NO | | NULL | |
在Eclipse中的实现代码:
包和类的创建图
1:
创建JDBC连接
package sub.loading.drive;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Driver {
private static final String URL = "jdbc:mysql://127.0.0.1:3306/subway";
private static final String USER = "root";
private static final String PASSWORD = "数据库的登录密码"; //用户名和密码是自己所设置的
private static Connection conn = null;
static {
//1.加载驱动程序
try {
Class.forName("com.mysql.jdbc.Driver");
//2.获得数据库的连接
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() {
return conn;
}
}
2:创建火车票 和 用户变量
1)火车票信息变量
package model;
public class Element {
private Integer id; //线路ID
private String terminal; //线路起点-终点
private Integer price; //票价
private Integer surplus; //余票数量
//get set方法赋值 的 获取值
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getTerminal() {
return terminal;
}
public void setTerminal(String terminal) {
this.terminal = terminal;
}
public Integer getPrice() {
return price;
}
public void setPrice(Integer price) {
this.price = price;
}
public Integer getSurplus() {
return surplus;
}
public void setSurplus(Integer surplus) {
this.surplus = surplus;
}
}
2)用户信息变量
package model;
public class ElementUsers {
private Integer id; //用户ID
private String client; //用户姓名
private String password; //用户密码
private String terminal; //用户选择的线路名
//get,set方法
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getClient() {
return client;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public void setClient(String client) {
this.client = client;
}
public String getTerminal() {
return terminal;
}
public void setTerminal(String terminal) {
this.terminal = terminal;
}
}
3:创建M(Model)层
package sub.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import model.Element;
import model.ElementUsers;
import sub.loading.drive.Driver;
/*
* 模型层
*/
public class Dao {
//增添路线, 增添到末尾
public void addTicket(Element e) throws Exception{
Connection conn = Driver.getConnection(); //获得连接
String sql = "" +
"INSERT INTO tickets " +
" (terminal, price, surplus) " +
" VALUES(" +
"?, ?, ?); "; //也可使用StringBuilder的append()
//预编译,将sql加载,不直接执行,调用execute()时执行
PreparedStatement ptmt = conn.prepareStatement(sql);
//导入数据
ptmt.setString(1, e.getTerminal());
ptmt.setInt(2, e.getPrice());
ptmt.setInt(3, e.getSurplus());
ptmt.execute();
}
//更新路线, 根据id实现
public void updateTicket(Element e) throws SQLException {
Connection conn = Driver.getConnection(); //获得连接
String sql = "" +
" UPDATE tickets " +
" SET terminal=?, price=?, surplus=? " +
" where id=?; ";
PreparedStatement ptmt = conn.prepareStatement(sql);
ptmt.setString(1, e.getTerminal());
ptmt.setInt(2, e.getPrice());
ptmt.setInt(3, e.getSurplus());
ptmt.setInt(4, e.getId());
ptmt.execute();
}
//删除路线, 根据id删除
public void delTicket(Element e) throws SQLException {
Connection conn = Driver.getConnection(); //获得连接
String sql = "" +
" DELETE FROM tickets " +
" where id=?;";
PreparedStatement ptmt = conn.prepareStatement(sql);
ptmt.setInt(1, e.getId());
ptmt.execute();
}
//查询所有路线
public List<Element> queryTicket() throws SQLException {
Connection conn = Driver.getConnection(); //获得连接
StringBuilder sql = new StringBuilder();
sql.append("SELECT * FROM tickets; ");
PreparedStatement ptmt = conn.prepareStatement(sql.toString());
ResultSet rs = ptmt.executeQuery();
List<Element> result = new ArrayList<Element>();
Element e = null;
while(rs.next()) {
e = new Element();
e.setId(rs.getInt("id"));
e.setTerminal(rs.getString("terminal"));
e.setPrice(rs.getInt("price"));
e.setSurplus(rs.getInt("surplus"));
result.add(e);
}
return result;
}
//创建用户,并选票,对应余票减一
public void setUsers(ElementUsers eu) throws SQLException {
Connection conn = Driver.getConnection(); //获得连接
StringBuilder sql = new StringBuilder();
sql.append(" INSERT users(client, password, terminal) VALUES(?, ?, ?);");
PreparedStatement ptmt = conn.prepareStatement(sql.toString());
ptmt.setString(1, eu.getClient());
ptmt.setString(2, eu.getPassword());
ptmt.setString(3, eu.getTerminal());
ptmt.execute();
StringBuilder sql2 = new StringBuilder();
sql2.append(" UPDATE tickets SET surplus=surplus-1 WHERE id=? ;");
PreparedStatement ptmt2 = conn.prepareStatement(sql2.toString());
Integer ter = Integer.valueOf(eu.getTerminal());
System.out.println(ter);
ptmt2.setInt(1, ter);
ptmt2.execute();
}
//更新用户目的地
public void updateUsersTicket(List<Map<String, String>> params, ElementUsers eu) throws SQLException {
Connection conn = Driver.getConnection(); //获得连接
StringBuilder sql = new StringBuilder();
sql.append("UPDATE users SET terminal=? WHERE 1=1 ");
if (params != null && params.size() > 0) {
for (int i = 0; i < params.size(); i++) {
Map<String, String> map = params.get(i);
sql.append(" and" + " " + map.get("name") + " "
+ map.get("rela") + " " + map.get("value") + " ");
}
}
PreparedStatement ptmt = conn.prepareStatement(sql.toString());
ptmt.setString(1, eu.getTerminal());
ptmt.execute();
}
//删除用户
public void delUsers(ElementUsers eu) throws SQLException {
Connection conn = Driver.getConnection(); //获得连接
String sql = "" +
" DELETE FROM users " +
" where id=?";
PreparedStatement ptmt = conn.prepareStatement(sql);
ptmt.setInt(1, eu.getId());
ptmt.execute();
}
//查询所有用户信息
public List<ElementUsers> queryUsers() throws SQLException {
Connection conn = Driver.getConnection(); //获得连接
StringBuilder sql = new StringBuilder();
sql.append("SELECT a.id, a.client, a.password, b.terminal FROM users AS a LEFT JOIN "
+ "tickets AS b ON a.terminal = b.id ;"); //运用LEFT JOIN左外链接,以便将线路id 用
//具体线路名称打印出
PreparedStatement ptmt = conn.prepareStatement(sql.toString());
ResultSet rs = ptmt.executeQuery();
List<ElementUsers> result = new ArrayList<ElementUsers>();
ElementUsers eu = null;
while(rs.next()) {
eu = new ElementUsers();
eu.setId(rs.getInt("id"));
eu.setClient(rs.getString("client"));
eu.setPassword(rs.getString("password"));
eu.setTerminal(rs.getString("terminal"));
result.add(eu);
}
return result;
}
}
4:创建控制层
package sub.action;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import model.Element;
import model.ElementUsers;
import sub.dao.Dao;
/*
* 控制层
*/
public class Action {
//增路线
public void add(Element e) throws Exception {
Dao dao = new Dao();
dao.addTicket(e);
}
//改路线
public void update(Element e) throws SQLException {
Dao dao = new Dao();
dao.updateTicket(e);
}
//删路线
public void del(Element e) throws SQLException {
Dao dao = new Dao();
dao.delTicket(e);
}
//查路线
public List<Element> query() throws SQLException {
Dao dao = new Dao();
return dao.queryTicket();
}
//增用户
public void addUsers(ElementUsers eu) throws Exception {
Dao dao = new Dao();
dao.setUsers(eu);
}
//改用户目的地
public void updateUsers(List<Map<String, String>> params, ElementUsers eu) throws SQLException {
Dao dao = new Dao();
dao.updateUsersTicket(params, eu);
}
//删用户
public void delUsres(ElementUsers eu) throws SQLException {
Dao dao = new Dao();
dao.delUsers(eu);
}
//查所有用户
public List<ElementUsers> queryUsers() throws SQLException {
Dao dao = new Dao();
return dao.queryUsers();
}
}
5:创建视图层
package sub.view;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Scanner;
import sub.dao.Dao;
import model.Element;
import model.ElementUsers;
/*
* 视图层
*/
public class View {
private static final String CONTEXT = "欢迎进入火车站:\n" +
"下面是本站的功能列表:\n"+
"[MAIN/M]:主菜单 \n" +
"[ADD/A]:添加线路信息 \n"+
"[UPDATE/U]:更新线路信息 \n" +
"[DELETE/D]:删除线路 \n"+
"[QUERY/Q]:查看终点的全部信息 \n" +
"[SET/S]:创建用户账号 \n" +
"[CHANGE/C]:更该目的地 \n" +
"[INNER/I]:员工系统 \n" +
"[EXIT/E]:退出火车票系统 ";
private static final String CONTEXTUSERS = "欢迎进入员工内部系统: \n" +
"[MAIN/M]:主菜单 \n" +
"[DELETE/D]:删除用户 \n"+
"[QUERY/Q]:查看用户全部信息 \n" +
"[EXIT/E]:退出员工系统 ";
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
System.out.println(CONTEXT);
Scanner scan = new Scanner(System.in);
String st = null;
boolean a = true;
while (a) {
System.out.println("请输入您的选择(火车站界面):");
st = scan.next().substring(0, 1).toUpperCase();
Element e = new Element();
Dao dao = new Dao();
switch (st) {
case "M": {
System.out.println(CONTEXT);
break;
}
case "A": {
int step = 1;
while (true) {
if (step == 1) {
System.out.println("请输入线路[名称], 如'成都-都江堰':");
step++;
} else if (step == 2) {
e.setTerminal(scan.next());
System.out.println("请输入线路[价格]:");
step++;
} else if (step == 3) {
e.setPrice(scan.nextInt());
System.out.println("请输入线路[余票]:");
step++;
} else if (step == 4) {
e.setSurplus(scan.nextInt());
try {
dao.addTicket(e);
System.out.println("添加线路成功!");
} catch (Exception e2) {
// TODO: handle exception
e2.printStackTrace();
System.out.println("添加线路失败!");
}
break;
}
}
break;
}
case "U": {
int step = 1;
while (true) {
if (step == 1) {
System.out.println("请输入要更新线路的ID:");
int id = scan.nextInt();
e.setId(id);
System.out.println("请输入线路的[名称], 如'成都-都江堰'");
step++;
} else if (step == 2) {
e.setTerminal(scan.next());
System.out.println("请输入线路[价格]:");
step++;
} else if (step == 3) {
e.setPrice(scan.nextInt());
System.out.println("请输入线路[余票]:");
step++;
} else if (step == 4) {
e.setSurplus(scan.nextInt());
try {
dao.updateTicket(e);
System.out.println("更新线路成功!");
} catch (Exception e2) {
// TODO: handle exception
e2.printStackTrace();
System.out.println("更新线路失败!");
}
break;
}
}
break;
}
case "D": {
System.out.println("请输入要删除线路的ID:");
int id = scan.nextInt();
e.setId(id);
dao.delTicket(e);
System.out.println("删路线成功!");
break;
}
case "Q": {
List<Element> q = new ArrayList<Element>();
q = dao.queryTicket();
System.out.println("ID 线路 价格/张 余票");
for (int i = 0; i < q.size(); i++) {
System.out.println(q.get(i).getId() + "\t"
+ q.get(i).getTerminal() + " \t"
+ q.get(i).getPrice() + " \t"
+ q.get(i).getSurplus());
}
break;
}
case "S": {
int step = 1;
ElementUsers eu = new ElementUsers();
while (true) {
if (step == 1) {
System.out.println("请输入用户[名称]:");
step++;
} else if (step == 2) {
eu.setClient(scan.next());
System.out.println("请输入用户[密码]:");
step++;
} else if (step == 3) {
eu.setPassword(scan.next());
System.out.println("请输入目的地(线路ID):");
step++;
} else if (step == 4) {
eu.setTerminal(scan.next());
try {
dao.setUsers(eu);
System.out.println("创建用户成功!");
} catch (Exception e2) {
// TODO: handle exception
e2.printStackTrace();
System.out.println("创建用户失败!");
}
break;
}
}
break;
}
case "C": {
List<Map<String, String>> params = new ArrayList<Map<String, String>>();
Map<String, String> map = new HashMap<String, String>();
ElementUsers eu = new ElementUsers();
System.out.println("请输入用户名:");
String usersName = scan.next();
map.put("name", "client");
map.put("rela", "=");
map.put("value", "'" + usersName + "'");
params.add(map);
System.out.println("请输入密码:");
String password = scan.next();
map.put("name", "password");
map.put("rela", "=");
map.put("value", "'" + password + "'");
params.add(map);
System.out.println("请输入目的地:");
eu.setTerminal(scan.next());
try {
dao.updateUsersTicket(params, eu);
System.out.println("更改目的地成功!");
}catch(Exception e3) {
e3.printStackTrace();
System.out.println("请核实用户名或密码!");
}
break;
}
case "I": {
System.out.println("请输入登录密码(默认123456):");
String password = scan.next();
if (password.equals("123456")) {
String st2 = null;
boolean b = true;
System.out.println(CONTEXTUSERS);
while (b) {
System.out.println("请输入您的选择(员工界面):");
st2 = scan.next().substring(0, 1).toUpperCase();
ElementUsers eu = new ElementUsers();
switch (st2) {
case "M": {
System.out.println(CONTEXT);
break;
}
case "D": {
System.out.println("请输入要删除用户的ID:");
int id = scan.nextInt();
eu.setId(id);
dao.delUsers(eu);
System.out.println("删用户成功!");
break;
}
case "Q": {
List<ElementUsers> q = new ArrayList<ElementUsers>();
q = dao.queryUsers();
System.out.println("ID 用户名 密码 目的地");
for (int i = 0; i < q.size(); i++) {
System.out.println(q.get(i).getId() + "\t"
+ q.get(i).getClient() + "\t"
+ q.get(i).getPassword() + "\t"
+ q.get(i).getTerminal());
}
break;
}
case "E": {
b = false;
System.out.println("成功退出!");
break;
}
}
}
} else {
System.out.println("输入密码错误!");
break;
}
break;
}
case "E": {
a = false;
System.out.println("成功退出!");
break;
}
}
}
scan.close();
}
}