package shiyan4;
import java.sql.*;
import java.util.Scanner;
public class TableTest {
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
System.out.println("===emplyees表===");
System.out.println("1.创建表");
System.out.println("2.添加记录");
System.out.println("3.更新记录");
System.out.println("4.删除记录");
System.out.println("5.查询表记录");
System.out.println("6.按工资查询");
// 循环结束结束标记,初值为Y
String s = "Y";
while (s.equals("Y")) {
// 输入商品序列号
System.out.println("请输入要进行成操作：");
Scanner sc = new Scanner(System.in);
int no = sc.nextInt();
switch (no) {
case 1:
createTable();
break;
case 2:
insertData();
break;
case 3:
updateData();
break;
case 4:
deleteData();
break;
case 5:
queryAllData();
break;
case 6:
queryByWageDesc();
break;
}
System.out.println("需要继续请输入Y，否则输入N");
s = sc.next();
if (s.equals("N")) {
System.out.println("结束");
break;
}
}
}
// 创建表
public static void createTable() throws Exception {
Connection con=DBConfig.getConnection();
Statement st=con.createStatement();
// 创建emplyees数据表
String createTable="create table emplyees" +
"(id VARCHAR(10) PRIMARY KEY," +
"name VARCHAR(40)," +
"sex VARCHAR(2)," +
"age int," +
"wage int," +
"title varchar(10)" +
")";
// 判断表是否存在
ResultSet rs=con.getMetaData().getTables(null, null, "emplyees", null);
if(!rs.next()) {
st.executeUpdate(createTable);
System.out.println("表创建成功");
}
else
{
System.out.println("表已经存在");
}
DBConfig.closeConnection(rs, st, con);
}
// 添加记录
public static void insertData() throws Exception {
Connection con=DBConfig.getConnection();
Statement st=con.createStatement();
// 插入数据，如果数据存在，则忽略此次插入，通过主键检查是否存在
String sql="insert ignore into emplyees(id,name,sex,age,wage,title) values"
+ "('1001','丁卫国', '男', 25, 2500, '助工'),"
+ "('1002', '张小华', '女', 30, 1000, '工程师'),"
+ "('1003', '宁涛', '男', 50, 6000,'高工')";
int num=st.executeUpdate(sql);
if (num > 0) {
System.out.println("插入数据成功！");
}else {
System.out.println("插入数据失败！");
}
DBConfig.closeConnection(null, st, con);
}
// 更新记录
public static void updateData() throws Exception {
String sql="UPDATE emplyees "
+ "SET wage = CASE "
+ " WHEN age > 45 THEN wage * 1.15 "
+ " ELSE wage * 1.1 "
+ "END";
Connection con=DBConfig.getConnection();
Statement st=con.createStatement();
int num=st.executeUpdate(sql);
if(num>0) {
System.out.println("更新成功");
}else {
System.out.println("更新失败");
}
DBConfig.closeConnection(null, st, con);
}
//删除记录
public static void deleteData() throws Exception {
Connection con=DBConfig.getConnection();
Statement st=con.createStatement();
String sql="delete from emplyees where wage>1500";
int num=st.executeUpdate(sql);
if(num>0) {
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
DBConfig.closeConnection(null, st, con);
}
// 查找
public static void queryAllData() throws Exception {
String querysql = "select * from emplyees";
Connection con = DBConfig.getConnection();
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(querysql);
if (rs.next() ) {
rs.previous();
System.out.println("编号\\t姓名\\t性别\\t年龄\\t工资\\t职称");
while (rs.next()) {
String id = rs.getString("id");
String name = rs.getString("name");
String sex = rs.getString("sex");
int age = rs.getInt("age");
int wage = rs.getInt("wage");
String title = rs.getString("title");
System.out.println(id + "\t" + name + "\t" + sex + "\t" + wage + "\t" + title);
}
} else {
System.out.println("表中无记录");
}
DBConfig.closeConnection(rs, st, con);
}
// 按工资降序查找
public static void queryByWageDesc() throws Exception {
String querysql="select * from emplyees order by wage desc";
Connection con=DBConfig.getConnection();
Statement st=con.createStatement();
ResultSet rs=st.executeQuery(querysql);
System.out.println("编号\\t姓名\\t性别\\t年龄\\t工资\\t职称");
while (rs.next()) {
String id=rs.getString("id");
String name=rs.getString("name");
String sex=rs.getString("sex");
int age=rs.getInt("age");
int wage=rs.getInt("wage");
String title=rs.getString("title");
System.out.println(id+"\t"+name+"\t"+sex+"\t"+wage+"\t"+title);
}
DBConfig.closeConnection(rs, st, con);
}
}
