本文实例总结了java jdbc连接数据库常见操作。分享给大家供大家参考,具体如下:
db.properties
配置文件(mysql数据库)
1
2
3
4
5
6
7
8
|
# db.properties jdbc.driver=com.mysql.jdbc.driver jdbc.url=jdbc:mysql: //localhost:3306/demo?useunicode=true&characterencoding=utf-8 jdbc.username=root jdbc.password= 123456 # paramter for basicdatasource initsize= 2 maxactive= 2 |
db.properties
配置文件(oracle数据库)
1
2
3
4
5
6
7
8
|
# db.properties jdbc.driver=oracle.jdbc.oracledriver jdbc.url=jdbc:oracle:thin:localhost: 1521 :orcl jdbc.username=root jdbc.password= 123456 # paramter for basicdatasource initsize= 2 maxactive= 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