JDBC与数据库练习

2019独角兽企业重金招聘Python工程师标准>>>

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Types;

import java.text.SimpleDateFormat;

import java.util.Scanner;

public class Exam {

// 加载驱动字符串

private static final String Driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

// 连接字符串

private static final String URL = "jdbc:sqlserver://localhost:1433;databaseName=Northwind";

// 用户名

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 CallableStatement cst = 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 void getEmployeeInfo() {

// 捕捉异常

try {

pst = this

.getConnection()

.prepareStatement(

"select EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate from Employees where LastName=? and FirstName=?");

System.out.println("请输入要查询employeeLastName:");

pst.setString(1, scan.nextLine());

System.out.println("请输入要查询employeeFirstName:");

pst.setString(2, scan.nextLine());

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

rs = pst.executeQuery();

System.out.println("职工编号" + "    " + "姓名" + "    " + "职位" + "       "

+ "学历" + "    " + "出生日期");

while (rs.next()) {

System.out.println(rs.getString(1) + " " + rs.getString(2)

+ "    " + rs.getString(3) + "    " + rs.getString(4)

+ rs.getString(5) + "    "

+ sdf.format(rs.getObject(6)));

}

} catch (Exception e) {

// 异常处理

e.printStackTrace();

}

}

// 建立获取职员所在城市的信息

public void getEmployeeCity() {

try {

pst = this.getConnection().prepareStatement(

"select * from Customers where City=?");

System.out.println("请输入要查询employeeCity:");

pst.setString(1, scan.nextLine());

rs = pst.executeQuery();

while (rs.next()) {

System.out.println(rs.getString(1) + "    " + rs.getString(2)

+ "    " + rs.getString(3) + "    " + rs.getString(4)

+ "   " + rs.getString(5));

}

} catch (Exception e) {

e.printStackTrace();

}

}

/**

* 建立获取每个产品的销售额的方法

* 

* 

*/

public void getSale() {

try {

pst = this

.getConnection()

.prepareStatement(

"select UnitPrice*Quantity from [Order Details] where ProductID=?");

System.out.println("请输入要查询ProductID:");

// 设置参数

pst.setInt(1, scan.nextInt());

// 执行过程语句

rs = pst.executeQuery();

// 遍历结果集

System.out.println("销售额");

while (rs.next()) {

System.out.println(rs.getString(1)

);

}

} catch (Exception e) {

e.printStackTrace();

}

}

/**

* 建立获取客户订单信息的方法

* 

* 

*/

public void getOrderDetails() {

try {

String sql1 = "select ProductID,UnitPrice,Quantity,Discount " +

"from [Order Details] where OrderID=?";

pst = this.getConnection().prepareStatement(sql1);

// 设置参数

System.out.println("请输入订单号：");

pst.setInt(1, scan.nextInt());

// 执行过程语句

rs = pst.executeQuery();

// 遍历结果集

System.out.println("商品编号"+" "+"单价"+"    "+"数量"+"     "+"折扣");

while (rs.next()) {

System.out.println(rs.getString(1) +"   " + rs.getString(2) + "    "

+ rs.getString(3) + "    " + rs.getString(4));

}

} catch (Exception e) {

e.printStackTrace();

}

}

/**

* 建立删除客户订单的方法

*/

public void deleteOrder() {

try {

String sql = "delete from Orders where EmployeeID=? and CustomerID=?";

pst = this.getConnection().prepareStatement(sql);

System.out.println("请输入员工ID：");

pst.setInt(1, scan.nextInt());

System.out.println("请输入客户ID：");

pst.setString(2, scan.next());

int line = pst.executeUpdate();

if (line > 0) {

System.out.println("删除成功");

}

} catch (Exception e) {

e.printStackTrace();

}

}

/**

* 建立调用存储过程的方法

*/

public void callProcedure() {

try {

// 使用connection来创建一个Callablestatement对象

cst = this.getConnection().prepareCall("{call sp_mysproc2(?)}");

System.out.println("请输入页码数：");

cst.setInt(1, scan.nextInt());

cst.registerOutParameter(1, Types.INTEGER);

rs = cst.executeQuery();

System.out.println("OrderId" + "  " + "ProductID" + " "

+ "UnitPrice" + " " + "Quantity" + " "

+ "Discount");

while (rs.next()) {

System.out.println(rs.getString(1) + "     " + rs.getString(2)

+ "     " + rs.getString(3) + "     " + rs.getString(4)

+ "     " + rs.getString(5));

}

} catch (Exception e) {

e.printStackTrace();

}

}

/**

* 建立更改订单信息的方法

*/

public void alertOrder() {

try {

String sql2 =

"update Orders set ShippedDate=?,Freight=? where OrderID=?";

pst = this.getConnection().prepareStatement(sql2);

System.out.println("请输入订购日期：");

pst.setString(1, scan.nextLine());

System.out.println("请输入货运价格：");

pst.setString(2, scan.nextLine());

System.out.println("请输入要修改的订单号：");

pst.setInt(3, scan.nextInt());

int line = pst.executeUpdate();

if (line > 0) {

System.out.println("更新成功");

}

} catch (Exception e) {

e.printStackTrace();

}

}

/**

* 建立关闭数据库资源的方法

*/

public void free() {

if (rs != null) {

try {

rs.close();

} catch (Exception e) {

e.printStackTrace();

}

if (pst != null) {

try {

pst.close();

} catch (Exception e) {

e.printStackTrace();

}

if (con != null) {

try {

con.close();

} catch (Exception e) {

e.printStackTrace();

}

}

}

}

}

public static void main(String[] args) {

try {

Exam ea = new Exam();

// ea.callProcedure();

// ea.getEmployeeCity();

ea.getEmployeeInfo();

ea.free();

} catch (Exception e) {

e.printStackTrace();

}

}

}

转载于:https://my.oschina.net/u/656507/blog/70754