import java.io.*;
import java.sql.*;
import java.util.*;
/**
* 测试Java程序利用JDBC API访问oracle数据库
* @author teacher
*
*/
public class MyJDBCDemo {
private static String url ;
private static String dbUser ;
private static String dbPassword ;
/**
* 读入filename指定的文件,并解析,把键值对中的数据取出
* 给全局变量url, dbUser, dbPassword赋值
* @param filename : 传入的文件名
*/
public static void getParam(String filename){
Properties propes = new Properties();
File file = new File(filename);
try {
FileInputStream fis = new FileInputStream(file);
//加载输入流指定的文件,数据放入键值对对象。
propes.load(fis);
//获取文件中url(key)对应的value,给全局变量赋值
url = propes.getProperty("url");
dbUser = propes.getProperty("dbUser");
dbPassword = propes.getProperty("dbPassword");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 连接数据库,获取emp表的数据并打印
*/
public static void getEmpData(){
Connection conn = null;//定义连接对象
String sql = "select * from from emp";//合法的sql语句,但是没有分号
try {
//利用DriverManager给连接对象赋值,
//需要三个参数:
//连接字符串;数据库的用户名;密码
conn = DriverManager
.getConnection(url,dbUser,dbPassword);//多态
Statement stmt = conn.createStatement();//多态
//传递sql语句到数据库中,并返回结果集
ResultSet rs = stmt.executeQuery(sql);
//指针的初始位置是第一行之前(BeforeFirst)。
//next()方法的功能是指针下移一行,并返回boolean值
//如果下移一行后,指针指向的这行有数据,返回true,否则返回false
while(rs.next()){
//每个循环体,指针指向一行
//id/name/age是列名,
//number类型用getInt()取值
//varchar2/char类型用getString()取值
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
System.out.println(id + "," + name + "," + age);
}
//关闭资源
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* @param args
*/
public static void main(String[] args) {
getParam("src/db_oracle.properties");
//getEmpData();
//boolean flag = login(1001,"1234");
//System.out.println(flag ? "登录成功" : "登录失败");
//boolean flag1 = insertUser(1030,"abcd","zhangwj","1234");
//System.out.println(flag1 ? "新增成功" : "新增失败");
//boolean flag2 = updateUser(1988,"lisi","5678");
//System.out.println(flag2 ? "更新成功" : "更新失败");
boolean flag3 = deleteUser(1020);
System.out.println(flag3 ? "删除成功" : "删除失败");
}
/**
* 实现判断id和pwd是否是合法用户
* select * from user_ning
where id = 1001 and password = '1234';
* @param id:用户id
* @param pwd: 用户password
* @return 如果是合法用户,返回true,否则返回false
*/
public static boolean login(int id, String pwd){
boolean flag = false;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String sql = "select * from user_ning " +
" where id =" + id + " and password = '"+pwd+"'";
try {
conn = DriverManager
.getConnection(url, dbUser, dbPassword);
stmt = conn.createStatement();
//如果id是pk,只能有一条记录或者没有。
rs = stmt.executeQuery(sql);
if (rs.next()){
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally{
try{
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (conn != null) conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
return flag;
}
/**
* 新增用户
* insert into user_ning values(1003,'abcd',
'zhangwj','12345678');
* @param id 用户编码
* @param pwd 用户密码
* @param name 用户姓名
* @param phone 用户电话
* @return 如果新增成功,返回true, 否则返回false
*/
public static boolean insertUser(
int id, String pwd, String name, String phone){
boolean flag = false;
Connection conn = null;
PreparedStatement stmt = null;
String sql = " insert into user_ning values(?,?,?,?)";
try{
conn = DriverManager
.getConnection(url,dbUser,dbPassword);
//stmt = conn.createStatement();
//stmt.executeQuery(sql);
stmt = conn.prepareStatement(sql);//预处理的语句对象
stmt.setInt(1, id);//把id填充第1个问号
stmt.setString(2, pwd);//用pwd填充第2个问号
stmt.setString(3, name);
stmt.setString(4, phone);
int n = stmt.executeUpdate();//执行
if (n == 1) flag = true;
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if (stmt != null) stmt.close();
if (conn != null) conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
return flag;
}
/**
* 更新指定用户的姓名和电话
* @param id 指定的用户
* @param name 更新后的姓名
* @param phone 更新后的电话
* @return 如果更新成功,返回true,否则返回false
*/
public static boolean updateUser(
int id, String name, String phone){
//update user_ning set name = 'zhangsan',
//phone = '5678' where id = 1001;
boolean flag = false;
String sql = "update user_ning set name = ?, " +
"phone = ? where id = ?";
Connection conn = null;
PreparedStatement stmt = null;
try{
conn = DriverManager.getConnection(url,dbUser,dbPassword);
stmt = conn.prepareStatement(sql);
stmt.setString(1, name);
stmt.setString(2, phone);
stmt.setInt(3, id);
int count = stmt.executeUpdate();//执行
if (count == 1){
flag = true;
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if (conn != null) conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
return flag;
}
/**
* 删除指定用户
* @param id 指定的用户
* @return 如果删除成功,返回true,否则返回false
*/
public static boolean deleteUser(int id){
//delete user_ning where id = 1988
boolean flag = false;
String sql = "delete user_ning where id = ?";
Connection conn = null;
PreparedStatement stmt = null;
try{
conn = DriverManager.getConnection(url,dbUser,dbPassword);
stmt = conn.prepareStatement(sql);
stmt.setInt(1, id);
int count = stmt.executeUpdate();//执行
System.out.println(count);
if (count == 1){
flag = true;
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if (conn != null) conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
return flag;
}
}
import java.sql.*;
import java.util.*;
/**
* 测试Java程序利用JDBC API访问oracle数据库
* @author teacher
*
*/
public class MyJDBCDemo {
private static String url ;
private static String dbUser ;
private static String dbPassword ;
/**
* 读入filename指定的文件,并解析,把键值对中的数据取出
* 给全局变量url, dbUser, dbPassword赋值
* @param filename : 传入的文件名
*/
public static void getParam(String filename){
Properties propes = new Properties();
File file = new File(filename);
try {
FileInputStream fis = new FileInputStream(file);
//加载输入流指定的文件,数据放入键值对对象。
propes.load(fis);
//获取文件中url(key)对应的value,给全局变量赋值
url = propes.getProperty("url");
dbUser = propes.getProperty("dbUser");
dbPassword = propes.getProperty("dbPassword");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 连接数据库,获取emp表的数据并打印
*/
public static void getEmpData(){
Connection conn = null;//定义连接对象
String sql = "select * from from emp";//合法的sql语句,但是没有分号
try {
//利用DriverManager给连接对象赋值,
//需要三个参数:
//连接字符串;数据库的用户名;密码
conn = DriverManager
.getConnection(url,dbUser,dbPassword);//多态
Statement stmt = conn.createStatement();//多态
//传递sql语句到数据库中,并返回结果集
ResultSet rs = stmt.executeQuery(sql);
//指针的初始位置是第一行之前(BeforeFirst)。
//next()方法的功能是指针下移一行,并返回boolean值
//如果下移一行后,指针指向的这行有数据,返回true,否则返回false
while(rs.next()){
//每个循环体,指针指向一行
//id/name/age是列名,
//number类型用getInt()取值
//varchar2/char类型用getString()取值
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
System.out.println(id + "," + name + "," + age);
}
//关闭资源
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* @param args
*/
public static void main(String[] args) {
getParam("src/db_oracle.properties");
//getEmpData();
//boolean flag = login(1001,"1234");
//System.out.println(flag ? "登录成功" : "登录失败");
//boolean flag1 = insertUser(1030,"abcd","zhangwj","1234");
//System.out.println(flag1 ? "新增成功" : "新增失败");
//boolean flag2 = updateUser(1988,"lisi","5678");
//System.out.println(flag2 ? "更新成功" : "更新失败");
boolean flag3 = deleteUser(1020);
System.out.println(flag3 ? "删除成功" : "删除失败");
}
/**
* 实现判断id和pwd是否是合法用户
* select * from user_ning
where id = 1001 and password = '1234';
* @param id:用户id
* @param pwd: 用户password
* @return 如果是合法用户,返回true,否则返回false
*/
public static boolean login(int id, String pwd){
boolean flag = false;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String sql = "select * from user_ning " +
" where id =" + id + " and password = '"+pwd+"'";
try {
conn = DriverManager
.getConnection(url, dbUser, dbPassword);
stmt = conn.createStatement();
//如果id是pk,只能有一条记录或者没有。
rs = stmt.executeQuery(sql);
if (rs.next()){
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally{
try{
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (conn != null) conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
return flag;
}
/**
* 新增用户
* insert into user_ning values(1003,'abcd',
'zhangwj','12345678');
* @param id 用户编码
* @param pwd 用户密码
* @param name 用户姓名
* @param phone 用户电话
* @return 如果新增成功,返回true, 否则返回false
*/
public static boolean insertUser(
int id, String pwd, String name, String phone){
boolean flag = false;
Connection conn = null;
PreparedStatement stmt = null;
String sql = " insert into user_ning values(?,?,?,?)";
try{
conn = DriverManager
.getConnection(url,dbUser,dbPassword);
//stmt = conn.createStatement();
//stmt.executeQuery(sql);
stmt = conn.prepareStatement(sql);//预处理的语句对象
stmt.setInt(1, id);//把id填充第1个问号
stmt.setString(2, pwd);//用pwd填充第2个问号
stmt.setString(3, name);
stmt.setString(4, phone);
int n = stmt.executeUpdate();//执行
if (n == 1) flag = true;
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if (stmt != null) stmt.close();
if (conn != null) conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
return flag;
}
/**
* 更新指定用户的姓名和电话
* @param id 指定的用户
* @param name 更新后的姓名
* @param phone 更新后的电话
* @return 如果更新成功,返回true,否则返回false
*/
public static boolean updateUser(
int id, String name, String phone){
//update user_ning set name = 'zhangsan',
//phone = '5678' where id = 1001;
boolean flag = false;
String sql = "update user_ning set name = ?, " +
"phone = ? where id = ?";
Connection conn = null;
PreparedStatement stmt = null;
try{
conn = DriverManager.getConnection(url,dbUser,dbPassword);
stmt = conn.prepareStatement(sql);
stmt.setString(1, name);
stmt.setString(2, phone);
stmt.setInt(3, id);
int count = stmt.executeUpdate();//执行
if (count == 1){
flag = true;
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if (conn != null) conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
return flag;
}
/**
* 删除指定用户
* @param id 指定的用户
* @return 如果删除成功,返回true,否则返回false
*/
public static boolean deleteUser(int id){
//delete user_ning where id = 1988
boolean flag = false;
String sql = "delete user_ning where id = ?";
Connection conn = null;
PreparedStatement stmt = null;
try{
conn = DriverManager.getConnection(url,dbUser,dbPassword);
stmt = conn.prepareStatement(sql);
stmt.setInt(1, id);
int count = stmt.executeUpdate();//执行
System.out.println(count);
if (count == 1){
flag = true;
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if (conn != null) conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
return flag;
}
}