package sqltest;
import java.sql.*;
import parameter.BaseCanShu;
public class PublicDbOracle {
static String id = "";
static int status = 0;
static Connection con = null;// 创建一个数据库连接
static PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
static ResultSet result = null;// 创建一个结果集对象
public static void main(String[] args) throws Exception {
// System.out.println(OracleSearchOrder ());
// System.out.println(UpdateStatus ());
}
/**
* 数据库连接开始部分
*/
public static void OracleOpen () throws Exception
{
Class.forName("oracle.jdbc.driver.OracleDriver");// 加载Oracle驱动程序
System.out.println("开始尝试连接数据库！");
String url = "jdbc:oracle:" + "thin:@127.0.0.1:1521:testdb";
String user = "test";// 用户名,系统默认的账户名
String password = "test";// 你安装时选设置的密码
con = DriverManager.getConnection(url, user, password);// 获取连接
// System.out.println("连接成功！");
}
/**
* 关闭数据库
*/
public static void OracleClose () throws Exception
{
try
{
// 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
// 注意关闭的顺序，最后使用的最先关闭
if (result != null)
{
result.close();
}
if (pre != null)
{
pre.close();
}
if (con != null)
{
con.close();
}
// System.out.println("数据库连接已关闭！");
}
catch (Exception e)
{
e.printStackTrace();
}
}
/**
* 查询
*/
public static String OracleSearchOrder() throws Exception {
BaseCanShu bcs = new BaseCanShu();
try {
OracleOpen();
String sql="select * from t_table t " +
"where t.name = ?" +
"and t.no like ?" +
"and t.pass = ?" +
"ORDER BY t.id DESC";
pre = con.prepareStatement(sql);
pre.setString(1,bcs.name);
pre.setString(2,bcs.no);
pre.setString(3,bcs.pass);
result=pre.executeQuery();// 执行查询，注意括号中不需要再加参数
if(result.next())
{
orderid = result.getString("t_no");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
OracleClose();
}
return id;
}
/**
* 修改销账表支付状态
*/
public int UpdateStatus() throws Exception {
BaseCanShu bcs = new BaseCanShu();
try {
OracleOpen();
String sql="update t_table t set " +
"t.onestatus = '1', t.twostatus = '2' " +
"where t.id =" +
"(select max(cc.id) from t_other cc " +
"where cc.no like ?" +
"and cc.name = ?)";
pre = con.prepareStatement(sql);
pre.setString(1,bcs.no);
pre.setString(2,bcs.name);
status = pre.executeUpdate();// 执行查询，注意括号中不需要再加参数
if(status == 1)
{
System.out.println("已更新");
}
else
{
System.out.println("未更新");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
OracleClose();
}
return status;
}