JDBC数据库连接及基础增删改查

package com.gfl.zhjs;


import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLConnection;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;


import net.sf.json.JSONObject;


public class JDBC {
private String user;
private String pass;
private String url;


private Connection conn = null;// 连接对象
private ResultSet rs = null;// 结果集对象
private Statement sm = null;


/**
* 构造函数获得数据库用户名和密码
* 
* @param user
* @param pass
*/
public JDBC(String user, String pass) {
this.user = user;
this.pass = pass;
this.url = "jdbc:oracle:thin:@192.168.11.61:1521:orcl";
}


/**
* 连接数据库
* 
* @return
*/
public Connection createConnection() {


String sDBDriver = "oracle.jdbc.driver.OracleDriver";


try {
Class.forName(sDBDriver).newInstance();
conn = DriverManager.getConnection(url, user, pass);
} catch (Exception e) {
System.out.println("数据库连接失败");
e.printStackTrace();
}
return conn;
}


/**
* 关闭数据库
* 
* @param conn
*/
public void closeConnection(Connection conn) {
try {
if (conn != null) {
conn.close();
}
} catch (Exception e) {
System.out.println("数据库关闭失败");
e.printStackTrace();
}
}


/**
* 插入数据
* 
* @param insert
*            插入语句
* @return
*/
public int insert(String insert) {
conn = createConnection();
// String insert = "insert into t_department values('D004','金融部')";
int re = 0;
try {
conn.setAutoCommit(false);// 事物开始


sm = conn.createStatement();
re = sm.executeUpdate(insert);
if (re < 0) { // 插入失败
conn.rollback(); // 回滚
sm.close();
closeConnection(conn);
return re;
}
conn.commit(); // 插入正常
sm.close();
closeConnection(conn);
return re;
} catch (Exception e) {
e.printStackTrace();
}
closeConnection(conn);
return 0;


}


/**
* 查询语句 返回结果集
* 
* @param select
* @return
*/
public ResultSet selectSql(String select) {
conn = createConnection();
try {
sm = conn.createStatement();
rs = sm.executeQuery(select);
return rs;
} catch (Exception e) {
e.printStackTrace();
}


return null;
}


/**
* 根据结果集输出
* 
* @param rs
*/
public void printRs(ResultSet rs) {
int columnsCount = 0;
boolean f = false;
try {
if (!rs.next()) {
return;
}
ResultSetMetaData rsmd = rs.getMetaData();
columnsCount = rsmd.getColumnCount();// 数据集的列数
for (int i = 0; i < columnsCount; i++) {
System.out.print(rsmd.getColumnLabel(i + 1) + "/t"); // 输出列名
}
System.out.println();


while (!f) {
for (int i = 1; i <= columnsCount; i++) {
System.out.print(rs.getString(i) + "/t");
}
System.out.println();
if (!rs.next()) {
f = true;
}
}
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
closeConnection(conn);
}




/**
* 向指定URL发送GET方法的请求
* 
* @param url
*            发送请求的URL
* @param param
*            请求参数，请求参数应该是 name1=value1&name2=value2 的形式。
* @return URL 所代表远程资源的响应结果
*/
public static String sendGet(String url, String param) {
String result = "";
BufferedReader in = null;
try {
String urlNameString = url + "?" + param;
URL realUrl = new URL(urlNameString);
// 打开和URL之间的连接
URLConnection connection = realUrl.openConnection();
// 设置通用的请求属性
connection.setRequestProperty("accept", "*/*");
connection.setRequestProperty("connection", "Keep-Alive");
connection.setRequestProperty("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1;SV1)");
// 建立实际的连接
connection.connect();
// // 获取所有响应头字段
// Map<String, List<String>> map = connection.getHeaderFields();
// // 遍历所有的响应头字段
// for (String key : map.keySet()) {
// System.out.println(key + "--->" + map.get(key));
// }
// 定义 BufferedReader输入流来读取URL的响应
in = new BufferedReader(new InputStreamReader(connection.getInputStream(), "utf-8"));
String line;
while ((line = in.readLine()) != null) {
result += line;
}
} catch (Exception e) {
System.out.println("发送GET请求出现异常！" + e);
e.printStackTrace();
}
// 使用finally块来关闭输入流
finally {
try {
if (in != null) {
in.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
return result;
}


/**
* 
* @param uri
* @param param
*            请求参数，请求参数应该是 name1=value1&name2=value2 的形式。
* @param charset
* @return
*/
public static String sendPost(String uri, String param) {
String result = null;
PrintWriter out = null;
InputStream in = null;
try {
URL url = new URL(uri);
HttpURLConnection urlcon = (HttpURLConnection) url.openConnection();
urlcon.setRequestProperty("Cache-Control", "no-cache");
urlcon.setDoInput(true);
urlcon.setDoOutput(true);
urlcon.setUseCaches(false);
urlcon.setRequestMethod("POST");
urlcon.connect();// 获取连接
out = new PrintWriter(urlcon.getOutputStream());
out.print(param);
out.flush();
in = urlcon.getInputStream();
BufferedReader buffer = new BufferedReader(new InputStreamReader(in, "UTF-8"));
StringBuffer bs = new StringBuffer();
String line = null;
while ((line = buffer.readLine()) != null) {
bs.append(line);
}
result = bs.toString();
} catch (Exception e) {
System.out.println("[请求异常][地址：" + uri + "][参数：" + param + "][错误信息：" + e.getMessage() + "]");
} finally {
try {
if (null != in)
in.close();
if (null != out)
out.close();
} catch (Exception e2) {
System.out.println("[关闭流异常][错误信息：" + e2.getMessage() + "]");
}
}
return result;
}


}