import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class Work21 {
// 加载驱动字符串
private static final String Driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
// 连接字符串
private static final String URL = "jdbc:sqlserver://localhost:1433;databaseName=person";
// 用户名
private static final String USER = "sa";
// 密码
private static final String PASSWORD = "593850582";
/**
* 数据库连接
*/
private Connection con=null;
private PreparedStatement pst=null;
private ResultSet rs=null;
private Statement stmt=null;
Scanner scan = new Scanner(System.in);
// 加载驱动类
static {
try {
Class.forName(Driver);
} catch (Exception e) {
e.printStackTrace();
}
}
// 建立连接的方法
public Connection getConnection() {
try {
con = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}
return con;
}
// 建立更新的方法
public int executeupdate() {
try {
pst = this
.getConnection()
.prepareStatement(
"update person set employeeSalary='4600' where employeeId = ?");
System.out.println("请输入要更新列表的employeeId:");
pst.setInt(1, scan.nextInt());
int line = pst.executeUpdate();
if (line > 0) {
System.out.println("更新数据成功");
}
return line;
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭result
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
// 关闭Statement
if (pst != null) {
try {
pst.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
// 关闭Connection
if (con != null) {
try {
con.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
}
return 0;
}
// 建立删除的方法
public int executedelete() {
try {
pst = this.getConnection().prepareStatement(
"delete from person where employeeId=?");
System.out.println("请输入要删除列表的employeeId:");
pst.setInt(1, scan.nextInt());
int line = pst.executeUpdate();
if (line > 0) {
System.out.println("删除数据成功");
}
return line;
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭result
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
// 关闭Statement
if (pst != null) {
try {
pst.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
// 关闭Connection
if (con != null) {
try {
con.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
}
return 0;
}
// 建立查询的方法
public int getQuery() {
//
try {
// 使用switch语句来判断语句执行的条件
switch (scan.nextInt()) {
case 1: {
stmt = con.createStatement();
rs = stmt.executeQuery("select * from person");
System.out.println("employeeId" + " " + "employeeName"
+ " " + "email" + " " + "employeeSalary" + " "
+ "departmentId");
while (rs.next()) {
System.out.println(rs.getInt(1) + " " + rs.getString(2)
+ " " + rs.getString(3) + " "
+ rs.getString(4) + " " + rs.getString(5));
}
System.out.println("查询全部已成功");
}
break;
case 2: {
pst = this.getConnection().prepareStatement(
"select * from person where employeeId=?");
System.out.println("please input your employeeId:");
pst.setInt(1, scan.nextInt());
rs = pst.executeQuery();
System.out.println("employeeId" + " " + "employeeName"
+ " " + "email" + " " + "employeeSalary" + " "
+ "departmentId");
while (rs.next()) {
System.out.println(rs.getInt(1) + " " + rs.getString(2)
+ " " + rs.getString(3) + " "
+ rs.getString(4) + " " + rs.getString(5));
}
System.out.println("依据employeeId查询成功");
}
break;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭result
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
// 关闭Statement
if (pst != null) {
try {
pst.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
// 关闭Connection
if (con != null) {
try {
con.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
}
return 0;
}
public int executeInsert() {
try {
// 获得Statement对象
pst = this
.getConnection()
.prepareStatement(
"insert into person values(4,'刘兴雨','125469873@qq.com','4000',3)");
int line = pst.executeUpdate();
if (line > 0) {
System.out.println("插入数据成功");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭result
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
// 关闭Statement
if (pst != null) {
try {
pst.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
// 关闭Connection
if (con != null) {
try {
con.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
}
return 0;
}
// 建立退出方法
public void exit() {
try {
System.exit(0);
} catch (Exception e) {
e.printStackTrace();
}
}
}