JDBC 连接MySQL实例详解
jdbc连接mysql
jdbc连接mysql
加载及注册jdbc驱动程序
class.forname("com.mysql.jdbc.driver");
class.forname("com.mysql.jdbc.driver").newinstance();
jdbc url 定义驱动程序与数据源之间的连接
标准语法:
<protocol(主要通讯协议)>:<subprotocol(次要通讯协议,即驱动程序名称)>:<data source identifier(数据源)>
mysql的jdbc url格式:
jdbc:mysql//[hostname][:port]/[dbname][?param1=value1][¶m2=value2]….
示例:jdbc:mysql://localhost:3306/sample_db?user=root&password=your_password
常见参数:
user 用户名
password 密码
autoreconnect 联机失败,是否重新联机(true/false)
maxreconnect 尝试重新联机次数
initialtimeout 尝试重新联机间隔
maxrows 传回最大行数
useunicode 是否使用unicode字体编码(true/false)
characterencoding 何种编码(gb2312/utf-8/…)
relaxautocommit 是否自动提交(true/false)
capitalizetypenames 数据定义的名称以大写表示
建立连接对象
string url="jdbc:mysql://localhost:3306/sample_db?user=root&password=your_password";
connection con = drivermanager.getconnection(url);
建立sql陈述式对象(statement object)
statement stmt = con.createstatement();
执行sql语句
executequery() string query = "select * from test"; resultset rs=stmt.executequery(query); 结果集resultset while(rs.next()) {rs.getstring(1);rs.getint(2);} executeupdate() string upd="insert into test (id,name) values(1001,xuzhaori)"; int con=stmt.executeupdate(upd); execute()
示例:
try { } catch(sqlexception sqle) { } finally { }
java类型和sql类型 技术手册p421
preparedstatement(预编语句)
preparedstatement stmt = conn.preparestatement("insert into test(id,name)values(?,?)");
stmt.setint(1,id);
stmt.setstring(2,name);
注:一旦设定语句的参数值后,就可以多次执行改语句,直到调用clearparameters()方法将他清除为止
callablestatement(预储程序)技术手册p430
jdbc2.0使用
resultset对象中的光标上下*移动
statement stmt = con.createstatement (resultset.type_scroll_sensitive, resultset.concur_read_only);
resultset rs=stmt.executequery("select * from test");
public statement createstatement(int resultsettype,int resultsetconcuttency) throws sqlexception
resultsettype
type_forward_only 只能使用next()方法。
type_scroll_sensitive 可以上下移动,可以取得改变后的值。
type_scroll_insensitive 可以上下移动。
resultsetconcuttency
concur_read_only 只读
concur_updatable resultset对象可以执行数据库的新增、修改、和移除
直接使用resultset对象执行更新数据
新增数据
statement stmt=con.createstatement(resultset.type_scroll_sensitive,resultset.concur_pudatable); resultset uprs=stmt.executequery("select * from test"); uprs.movetoinsertrow(); uprs.updateint(1,1001); uprs.updatestring(2,"许召日"); uprs.insertrow;
更新数据
statement stmt=con.createstatement(resultset.type_scroll_sensitive,resultset.concur_pudatable); resultset uprs=stmt.executequery("select * from test"); uprs.last(); uprs.updatestring("name","xuzhaori"); uprs.updaterow;
删除数据
statement stmt=con.createstatement(resultset.type_scroll_sensitive,resultset.concur_pudatable); resultset uprs=stmt.executequery("select * from test"); uprs.absolute(4); uprs.deleterow();
批处理
con.setautocommit(false); 关闭自动认可模式 statement stmt=con.createstatement(); int[] rows; stmt.addbatch("insert into test values(1001,xuzhaori)"); stmt.addbatch("insert into test values(1002,xuyalin)"); rows=stmt.executebatch(); con.commit(); 没有任何错误,执行批处理stmt.executebatch();
jndi-数据源(data source)与连接池(connection pool)
tomcat的jdbc数据源设置 技术手册p439
连接池工具-proxool var 0.8.3 技术手册p446
设置web.xml
<?xml version="1.0" encoding="iso-8859-1"?> <!--<?xml version="1.0" encoding="gb2312"?>--> <web-app xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" xsi:schemalocation="http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd" version="2.4"> …. <servlet> <servlet-name>servletconfigurator</servlet-name> <servlet-class>org.logicalcobwebs.proxool.configuration.servletconfigurator</servlet-class> <init-param> <param-name>propertyfile</param-name> <param-value>web-inf/classes/proxool.properties</param-value> </init-param> <load-on-startup>1</load-on-startup> </servlet> 后端统计端口添加下列 <servlet> <servlet-name>admin</servlet-name> <servlet-class>org.logicalcobwebs.proxool.admin.servlet.adminservlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>admin</servlet-name> <url-pattern>/admin</url-pattern> </servlet-mapping> …. </web-app>
配置proxool.properties
jdbc-0.proxool.alias=jspbook jdbc-0.proxool.driver-class=com.mysql.jdbc.driver jdbc-0.proxool.driver-url=jdbc:mysql://localhost:3306/sample_db?user=root&password=browser&useunicode=true&characterencoding=utf-8 jdbc-0.proxool.maximum-connection-count=10 jdbc-0.proxool.prototype-count=4 jdbc-0.proxool.house-keeping-test-sql=select current_date jdbc-0.proxool.verbose=true jdbc-0.proxool.statistics=10s,1m,1d 后端统计接口添加此行 jdbc-0.proxool.statistics-log-level=debug
使用proxool连接池
connection con = drivermanager.getconnection("proxool.jspbook"); statement stmt = con.createstatement(resultset.type_scroll_sensitive, resultset.concur_updatable); string query = "select * from employee"; resultset rs = stmt.executequery(query);
感谢阅读此文,希望能帮助到大家,谢谢大家对本站的支持!