JDBCUtils涉及三表查询应用

package com.athong.jdbc.jdbcutils;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class JDBCUtils {


// 数据库驱动-----对象
private static final String driverUrl = "oracle.jdbc.driver.OracleDriver";


// Oracle数据库连接地址
private static final String url = "jdbc:oracle:thin:@localhost:1521:ORCL";


// Oracle数据库登陆账号
private static final String username = "hong";


// Oracle数据库登陆密码
private static final String password = "123456";


private static Connection conn;
private static PreparedStatement ps;
private static ResultSet rs;


/***************************************************************************
* 创建数据库连接
* 
* @param args
*/
public static Connection getConnection() {


try {
// 1.加载数据库驱动
Class.forName(driverUrl);


// 2.获取数据库连接对象
conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
// 3.返回连接对象
return conn;
}


/**
* 获取数据库的结果集
* 
* @param args
*/
public void getResultList() {


// 1.获取数据库连接
JDBCUtils.getConnection();


// 2.封装SQL语句
String sql = "select * from people";


try {
// 3.执行SQL语句
ps = conn.prepareStatement(sql);


// 4.获取结果集
rs = ps.executeQuery();


// 5.遍历结果集
while (rs.next()) {
String p_number = rs.getString(1);
String p_name = rs.getString(2);
int p_age = rs.getInt(3);
int p_sex = rs.getInt(4);
System.out.println(p_number + ":" + p_name + ":" + p_age + ":"
+ p_sex);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {


// 5.关闭数据库连接
close();
}
}


/**
* 关闭数据库连接
* 
* @param args
*/
public static void close() {
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}


/***************************************************************************
* 向数据库添加数据
* 
* @param args
*/
public void insert(String p_number, String p_name, int p_age, int p_sex) {


// 1.获取连接对象
JDBCUtils.getConnection();
// 2.封装SQL语句
String sql = "insert into people(pnumber,pname,age,sex)values(?,?,?,?)";
// 3.执行SQL语句
try {
ps = conn.prepareStatement(sql);
ps.setString(1, p_number);
ps.setString(2, p_name);
ps.setInt(3, p_age);
ps.setInt(4, p_sex);
ps.executeUpdate();
System.out.println("添加数据成功！");
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 4.关闭连接
close();
}


}


/***************************************************************************
* 修改数据库中的数据
* 
* @param args
*/
public void update(String p_name, int p_age, int p_sex) {


// 1.获取数据库连接
JDBCUtils.getConnection();


// 2.封装SQL语句
String sql = "update people set pname = ?,age = ?,sex = ? where pnumber = 'dddd'";


// 3.执行SQL语句
try {
ps = conn.prepareStatement(sql);
ps.setString(1, p_name);
ps.setInt(2, p_age);
ps.setInt(3, p_sex);
ps.executeUpdate();
System.out.println("修改数据成功！");
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 4.关闭连接
close();
}


}


/***************************************************************************
* 
* @param args
*/
public void delete(String p_number) {


// 1.获取数据库连接
JDBCUtils.getConnection();


// 2.封装SQL语句
String sql = "delete from people where pnumber = ?";


// 3.执行SQL语句
try {
ps = conn.prepareStatement(sql);
ps.setString(1, p_number);
ps.executeUpdate();
System.out.println("删除数据成功!");
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 4.关闭数据库连接
close();
}
}


/***************************************************************************
* 三表查詢
* 
* @param args
*/
public void getThreeTableResultSet() {


// 1.获取数据库连接
JDBCUtils.getConnection();
// 2.封装SQL语句
String sql = "select * from tb_student1 stu inner join tb_class1 stc "
+ "on stu.classid=stc.classid inner join tb_score sts on stu.stuid=sts.stuid";
// 3.执行SQL语句
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
System.out.println("学号"+"\t"+"姓名"+"\t"+"班级"+"\t"+"分数");
while (rs.next()) {
int stuid = rs.getInt("stuid");
String stuname = rs.getString("stuname");
String classname = rs.getString("classname");
int scoredata = rs.getInt("scoredata");
System.out.println(stuid + "\t" + stuname + "\t" + classname
+ "\t" + scoredata);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 4.关闭数据库连接
close();
}


}


public static void main(String[] args) {
JDBCUtils jdbc = new JDBCUtils();


// JDBCUtils.insert("hoasdfasasdfa", "fffff", 12, 1002);
// JDBCUtils.update("456", 0, 1001);
// JDBCUtils.delete("cccc");
// JDBCUtils.getResultList();
// jdbc.delete("2222");
// jdbc.update("789", 30, 1001);
// jdbc.getResultList();
// jdbc.insert("2222","ddddd", 12, 1002);
jdbc.getThreeTableResultSet();
}
}