本文實例總結了java jdbc連接數據庫常見操作。分享給大家供大家參考，具體如下：
db.properties配置文件(mysql數據庫)
|
1
2
3
4
5
6
7
8
|
# db.propertiesjdbc.driver=com.mysql.jdbc.driverjdbc.url=jdbc:mysql://localhost:3306/demo?useunicode=true&characterencoding=utf-8jdbc.username=rootjdbc.password=123456# paramter for basicdatasourceinitsize=2maxactive=2 |
db.properties配置文件(oracle數據庫)
|
1
2
3
4
5
6
7
8
|
# db.propertiesjdbc.driver=oracle.jdbc.oracledriverjdbc.url=jdbc:oracle:thin:localhost:1521:orcljdbc.username=rootjdbc.password=123456# paramter for basicdatasourceinitsize=2maxactive=2 |
jdbc直接連接數據庫
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
|
package jdbc;import java.io.ioexception;import java.io.inputstream;import java.sql.connection;import java.sql.drivermanager;import java.sql.sqlexception;import java.util.properties;/** * jdbc連接數據庫管理工具類 */public class jdbc { static string driver; static string url; static string username; static string password; static { try { properties cfg = new properties(); inputstream in = jdbc.class.getclassloader().getresourceasstream("db.properties"); cfg.load(in); //將文件內容加載到properties對象中(以散列表形式存在) driver = cfg.getproperty("jdbc.driver"); url = cfg.getproperty("jdbc.url"); username = cfg.getproperty("jdbc.username"); password = cfg.getproperty("jdbc.password"); in.close(); } catch (ioexception e) { e.printstacktrace(); throw new runtimeexception(e); } } /** * 創建數據庫連接 */ public static connection getconnection() { try { class.forname(driver); //注冊驅動 connection conn = drivermanager.getconnection(url, username, password); return conn; } catch (exception e) { e.printstacktrace(); throw new runtimeexception(e); } } /* * 關閉數據庫的連接 */ public static void close(connection conn) { if (conn != null) { try { conn.close(); } catch (sqlexception e) { e.printstacktrace(); } } }} |
通過連接池連接數據庫
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
|
package jdbc;import java.io.ioexception;import java.io.inputstream;import java.sql.connection;import java.sql.sqlexception;import java.util.properties;import org.apache.commons.dbcp.basicdatasource;/** * 連接池版本的數據庫連接管理工具類 */public class dbutils { private static string driver; private static string url; private static string username; private static string password; private static int initsize; private static int maxactive; private static basicdatasource dbs; static { dbs = new basicdatasource(); properties cfg = new properties(); try { inputstream in = dbutils.class.getclassloader().getresourceasstream("db.properties"); cfg.load(in); // 初始化參數 driver = cfg.getproperty("jdbc.driver"); url = cfg.getproperty("jdbc.url"); username = cfg.getproperty("jdbc.username"); password = cfg.getproperty("jdbc.password"); initsize = integer.parseint(cfg.getproperty("initsize")); maxactive = integer.parseint(cfg.getproperty("maxactive")); in.close(); // 初始化連接池 dbs.setdriverclassname(driver); dbs.seturl(url); dbs.setusername(username); dbs.setpassword(password); dbs.setinitialsize(initsize); dbs.setmaxactive(maxactive); } catch (ioexception e) { e.printstacktrace(); throw new runtimeexception(e); } } /** * 創建數據庫連接,從連接池中獲取連接,如果連接池滿了,則等待. */ public static connection getconnection() { try { connection conn = dbs.getconnection(); return conn; } catch (sqlexception e) { e.printstacktrace(); throw new runtimeexception(e); } } /* * 關閉數據庫的連接,歸還到連接池 */ public static void close(connection conn) { if (conn != null) { try { conn.close(); } catch (sqlexception e) { e.printstacktrace(); } } } //回滾，僅在禁用自動提交時使用 public static void rollback(connection conn) { if (conn != null) { try { conn.rollback(); } catch (sqlexception e) { e.printstacktrace(); } } }} |
連接數據庫后的使用
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
connection conn=null;try{ conn=dbutils.getconnection(); statement st=conn.createstatement(); string sql="select id, name from people"; resultset rs=st.executequery(sql); while(rs.next()){ int id=rs.getint("id"); string name=rs.getstring("name"); system.out.println(id+","+name); } //結果集元數據 resultsetmetadata meta = rs.getmetadata(); int n = meta.getcolumncount(); //多少列 for(int i=1; i<=n; i++){ string name= meta.getcolumnname(i); //獲取列名 system.out.println(name); } rs.close();//釋放查詢結果 st.close();//釋放語句對象}catch(exception e){ e.printstacktrace();}finally { dbutils.close(conn);} |
預編譯sql執行 及 取消自動提交
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
try { conn = dbutils.getconnection(); conn.setautocommit(false); //取消自動提交, 后續手動提交 string sql="update people set name=? where id=? "; preparedstatement ps= conn.preparestatement(sql); //按照順序發送參數 ps.setstring(1, "lao wang"); ps.setint(2, 100); //執行"執行計劃" int n=ps.executeupdate(); conn.commit(); //手動提交} catch (exception e) { e.printstacktrace(); dbutils.rollback(conn); //異常回滾}finally{ dbutils.close(conn);} |
statement的addbatch(sql)和executebatch()方法可以批量執行sql。
|
1
2
3
4
5
|
statement st=conn.createstatement();st.addbatch(sql1); //sql1 添加到statement的緩存中st.addbatch(sql2);st.addbatch(sql3);int[] ary=st.executebatch(); //執行一批sql |
preparedstatement也支持批量參數的處理
|
1
2
3
4
5
6
7
8
|
preparedstatement ps = conn.preparestatement(sql);ps.setint(1, 1);ps.setstring(2, "wang");ps.addbatch(); //將參數添加到ps緩存區ps.setint(1, 2);ps.setstring(2, "li");ps.addbatch(); //將參數添加到ps緩存區int[] ary = ps.executebatch(); // 批量執行 |
preparedstatement ps = conn.preparestatement(sql);
還可以傳入第二個參數用以獲取自增主鍵或者序號自增的列
希望本文所述對大家java程序設計有所幫助。
原文鏈接：https://blog.csdn.net/n447194252/article/details/71123917
