wendyneil的博客 http://blogger.org.cn/blog/blog.asp?name=wendyneil wendyneil的博客 blogger.org.cn W3CHINA Blog webmaster@blogger.org.cn <![CDATA[JDBC数据源和连接池]]> http://blogger.org.cn/blog/more.asp?name=wendyneil&id=46715 wendyneil 2009/7/29 10:48:42 //Source:

//《精通Java Web整合开发》 刘斌 编著  电子工业出版社

// MySQL 参考手册

在JSP或者Servlet中使用JDBC时，每次使用前要建立连接、用完后又要关闭，这有点麻烦。当用户数量增加时，会在建立连接和销毁连接过程中耗费很多资源。这时我们可以采用数据源和连接池。数据库连接池在应用程序启动时就创建足够多的数据库连接，在JAVA程序需要访问数据库时就可以通过数据源获得一个空闲连接，用完后再释放到连接池中。

下面以Tomcat6.0和MySQL5.0为例讲讲数据源和连接池的配置：

  1. 将数据库JDBC Driver复制到tomcat/lib目录下；
  2. 在tomcat/conf/server.xml中配置数据源和连接池：

      <Host name="localhost"  appBase="webapps"
            unpackWARs="true" autoDeploy="true"
            xmlValidation="false" xmlNamespaceAware="false">

           <Context path="/WebRoot" docBase="WebRoot" debug="0" crosscontext="true" reloadable="true">

            <Resource name="jdbc/myDataSource" type="javax.sql.DataSource"

            username="root" password="123" driverClassName="com.mysql.jdbc.Driver"

            maxIdle="30" maxWait="10000"   maxActive="100" 

            url="jdbc:mysql://localhost:3306/test?autoReconnect=true"  />

</Context>

</Host>

添加的属性以<Context path=...>开头，置于<host> </host>之间

其中一些属性简介如下：

    <!-- maxActive: Maximum number of dB connections in pool. Make sure you
         configure your mysqld max_connections large enough to handle
         all of your db connections. Set to 0 for no limit.
         -->

    <!-- maxIdle: Maximum number of idle dB connections to retain in pool.
         Set to -1 for no limit.  See also the DBCP documentation on this
         and the minEvictableIdleTimeMillis configuration parameter.
         -->

    <!-- maxWait: Maximum time to wait for a dB connection to become available
         in ms, in this example 10 seconds. An Exception is thrown if
         this timeout is exceeded.  Set to -1 to wait indefinitely.
         -->

    <!-- username and password: MySQL dB username and password for dB connections  -->

    <!-- driverClassName: Class name for the old mm.mysql JDBC driver is
         org.gjt.mm.mysql.Driver - we recommend using Connector/J though.
         Class name for the official MySQL Connector/J driver is com.mysql.jdbc.Driver.
         -->
   
    <!-- url: The JDBC connection url for connecting to your MySQL dB.
         The autoReconnect=true argument to the url makes sure that the
         mm.mysql JDBC Driver will automatically reconnect if mysqld closed the
         connection.  mysqld by default closes idle connections after 8 hours.
         -->

  3. 在web.xml中配置对数据源连接池的引用

<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
    http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
    version="2.4">
  <description>MySQL Test App</description>
  <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/myDataSource</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
</web-app>
   4.  在JSP或Servlet中通过数据源访问数据库
<%@ page language="java" import="java.util.*" pageEncoding="gb2312"%>
<%@ page import="java.sql.*"%>
<%@ page import="javax.sql.DataSource"%>
<%@ page import="javax.naming.*"%>
<html>
 <head><title>通过数据源访问数据库</title>
 </head>
 <body>
  <h2>通过数据源访问数据库</h2><hr>
  <%
   Connection con=null;
   Statement stat=null;
   ResultSet rs=null;
   
   //从数据源连接池取得连接
   Context ctx=new InitialContext();
   DataSource ds=(DataSource)ctx.lookup("java:comp/env/jdbc/myDataSource");
   con=ds.getConnection();
   
   //查询数据库表
   stat=con.createStatement();
   String sql="select * from user";
   rs=stat.executeQuery(sql);
   
   while(rs.next())
   {
     out.println("<li>账号："+rs.getString(2).trim());
     out.println("密码："+rs.getString(3)+"</li>");
   }
   
   rs.close();
   stat.close();
   con.close();
  %>
 </body>
</html> 

 

   5. 在tomcat下，可以通过http://localhost:8080/WebRoot/dbtest.jsp访问到数据库

      
   

]]> <![CDATA[JDBC API简介（看看java.sql中的一些接口和类们）]]> http://blogger.org.cn/blog/more.asp?name=wendyneil&id=46702 wendyneil 2009/7/28 11:28:49 //Source:

//《精通Java Web整合开发》 刘斌 编著  电子工业出版社

// MySQL 参考手册

现在我们就对JDBC API作一些简单了解

1. Driver接口和DriverManager类

所有的JDBC驱动程序都必须实现Driver接口，DriverManager从它的名字来看便知道它的用途了，用来管理这些Drivers，另外它还为我们提供了建立在这些驱动之上的数据库连接（Connections）

DriverManager的常用方法

  • void registerDriver(Driver driver) 将指定的JDBC驱动程序注册到DriverManager中去
  • void deregisterDriver(Driver driver) 将指定的JDBC驱动程序从DriverManager中删除
  • Connection getConnection(String url,String user,String password)
  • Connection getConnection(String url) 返回Connection

2. Connection接口

Connection常用方法

    • void close() 关闭该连接
    • void commit() 提交事务
    • Statement createStatement() 建立并返回一个Statement对象
    • DatabaseMetaData getMetaData() 取得数据库的MetaData数据
    • boolean isClosed() 判断该连接是否已经关闭
    • PreparedStatement prepareStatement(String sql) 建立并返回一个PreparedStatement对象
    • void rollback() 事务回滚
    • void setAutoCommit(boolean autoCommit) 设置该Connection对象是否采用自动事务提交模式

 

3. Statement接口

Statement对象可以通过Connection对象的createStatement()获得

Statement常用方法

  • void close() 关闭该Statement对象并释放资源
  • ResultSet executeQuery(String sql) 执行指定的SQl语句并返回一个ResultSet对象
  • int executeUpdate(String sql) 执行指定SQL语句并返回所影响的行数
  • int getMaxRows() 返回该Statement对象所支持的ResultSet对象的最大行数
  • void setMaxRows(int max) 设定该Statement对象所支持的ResultSet对象的最大行数

4. PreparedStatement接口

建立了与数据库连接后，对于一些动态的SQL语句，可以用PreparedStatement对象执行。PreparedStatement对象可以通过Connection对象的prepareStatement()方法取得：

  1. 使用PreparedStatement对象的prepareStatement(String sql)方法建立一个PreparedStatement对象；
  2. 使用PreparedStatement对象的setXXX方法为参数赋值；
  3. 使用PreparedStatement对象的executeQuery()执行SQL语句并得到ResultSet对象

PreparedStatement对象常用方法如下：

  • ResultSet executeQuery()  执行查询并返回ResultSet对象
  • void setDate(int index,Date x)  为指定参数赋java.sql.Date类型值
  • void setDouble(int index,double x) 
  • void setInt(int index,int x)
  • void setString(int index,String x)

5. ResultSet接口

查询数据库的终极目标是想得到一个满足条件的结果记录集，然后再对该结果集的数据进行操作，上面所讲的Statement对象和PreparedStatement对象执行指定SQL语句后将返回代表结果记录集的ResultSet对象，此对象除了包含查询结果的记录集外，还维护着一个指向当前数据行的游标，通过该游标的位置来达到操作不同记录集的目的。

ResultSet对象的常用方法如下：

在ResultSet对象中的记录号和字段号都是从1开始的！！！

  • boolean absolute(int row)
  • void afterLast()
  • void beforeFirst()
  • void close()
  • boolean first()
  • boolean last()
  • boolean next()
  • boolean previous()
  • boolean isAfterLast()
  • boolean isBeforeFirst()
  • boolean isFirst()
  • boolean isLast()
  • int getRow()        返回游标指向的记录号
  • boolean getBoolean(int columIndex)
  • boolean getBoolean(String columnName)
  • Date getDate(int columnIndex)  从当前行中指定序号的字段中取得一个java.sql.Date类型的值
  • Date getDate(String columnName)  从当前行指定的字段名中取得一个java.sql.Date类型的值
  • double getDouble(int columnIndex)
  • double getDouble(String columnName)
  • int getInt(int columnIndex)
  • int getInt(String columnName)
  • long getLong(int columnIndex)
  • long getLong(String columnName)
  • String getString(int columnIndex)
  • String getString(String columnName)
]]> <![CDATA[JDBC基础知识]]> http://blogger.org.cn/blog/more.asp?name=wendyneil&id=46701 wendyneil 2009/7/28 10:55:58 //Source:

//《精通Java Web整合开发》 刘斌 编著  电子工业出版社

// MySQL 参考手册

// 《JSP2.0 技术手册》 林上杰 林康司 编著 电子工业出版社

这里谈谈刚刚学习的JDBC链接，我是第一次弄这个。

JDBC访问数据库有四个步骤：

   1. 将数据库的JDBC驱动加载到classpath中；

   2. 加载JDBC驱动，并将其注册到DriverManager中：

          采用Class.forName("……").newInstance():

      • 其中……代表各个数据库的Driver，如MySQL就是com.mysql.jdbc.Driver

   3. 建立数据库连接，获取Connection对象：

以MySQL为例：

String url="jdbc:mysql://localhost:3306/testDB?user=root&password=123&

useUnicode=true&characterEncoding=gb2312";

 Connection conn=DriverManager.getConnection(url);

这里我们要详细讲讲其中的一些methods

Class.forName("……").newInstance

这里我们为什么要在加载完驱动程序后，再调用newInstance()呢？是因为加载驱动时可能会出现“Driver not found”的错误提示信息，因为在用Class.forName()加载时，会碰到JDBC规范与某些JVM产生问题，因此我们采用示例的程序。

getConnection()

这个方法除了上面的用法外，还有一种：

String url="jdbc:mysql://localhost:3306/testDB";

String user="root";

String password="123";                //这里我们数据库root用户密码设为123

Connection conn=DriverManager.getConnection(url,user,password);

   4. 建立Statement对象或PreparedStatement对象：

//建立Statement对象

Statement stmt=conn.createStatement(); 

//建立PreparedStatement对象

String sql="select * from users where userName=?and password=?";

PreparedStatement pstmt=conn.prepareStatement(sql);

pstmt.setString(1,"admin");

pstmt.setString(2,"liubin");

   5. 执行SQL语句：

//执行静态SQL查询

String sql="select * from users";

ResultSet rs=stmt.executeQuery(sql);

//执行动态SQL查询

ResultSet rs=pstmt.executeQuery();

//执行insert、update、delete等语句，先定义sql

   6. 访问结果记录集ResultSet对象：

while(rs.next())

{

out.println("你的第一个字段内容为："+rs.getString(1));

out.println("你的第二个字段内容为："+rs.getString(2));

}

   7. 依次将ResultSet、Statement、PreparedStatement、Connection对象关闭：

rs.close();

stmt.close();

pstmt.close();

conn.close();

 

]]> <![CDATA[The Most Common Habits from more than 200 English Papers SECTION2]]> http://blogger.org.cn/blog/more.asp?name=wendyneil&id=43622 wendyneil 2009/3/9 20:31:50 Section 2


1) Some words have identical singular and plural forms and do not need an s added on to make them plural. These words include:
• literature (when referring to research)
• equipment,
• staff (referring to a group of people)
• faculty
2) Avoid redundancy in the following types of phrases frequently used by Chinese English writers
Instead of               Say             Or say
Research work            Research        Work
Limit condition          Limit           condition
Knowledge memory         Knowledge       Memory
Sketch map               Sketch          map
Layout scheme            Layout          scheme
Arrangement plan         Arrangement     plan
Output performance       Output          performance
Simulation results       results         simulation
Knowledge information    Knowledge       information
Calculation results      results         calculation
Application results      Results         Application


3) Certain words demand that the noun they modify is plural. These include different, various, and number words.
Don’t write               Instead write
Different node             Different nodes
Various method             Various methods
Two advantage              Two advantages
Fifteen thermocouple       Fifteen thermocouples


4) Never begin an English sentence with abbreviations and Arabic numerals such as Fig.and 8. Instead write Figure and Eight.


5) Do not write ‘by this way’. Instead write ‘by doing this’, or ‘using this method’.


6) Never write ‘How to…’ at the beginning of a sentence. (Don’t say it ither.)

No How to find the optimal parameter is the main objective.

Yes Determining how to find the optimal parameter is the main objective.


7) Do write ‘the results are shown in Figure 2’. Do not write ‘the results are showed as Figure 2’.


8) Italicize variables appearing in the text to differentiate them from words. This is especially important when the variables are English alphabets. Write ‘The graph shows t, a, and C as a function of time’. Do not write ‘The graph shows t, a, and C as a function of time’.


9) Refrain from using the word obviously in a technical paper in the following way No Obviously, detecting regimes by means of PMH maps is a novel method.[8]
Yes Detecting regimes by means of PMH maps is a novel method.[8]


10) International papers should not use location dependant terms such as ‘at home’, abroad’, ‘here’, ‘our country’ because the reader most likely is not Chinese and not in China. Instead, write ‘in China’.


11) Avoid overusing the phrases ‘that is to say’ and ‘namely’. Instead, try to convey your meaning in one sentence.


12) Do not use ‘too’ at the end of a written sentence, especially in a technical paper.

 

APPENDIX A


1. LIN Fushen, MENG Guang, ‘Dynamic Behavior of an Unbalanced and Warped Jeffcott Rotor with Asymmetric Stiffness’, The State Key Lab of Vibration, Shock and Noise Shanghai JiaotongUnversity, Shanghai 200030, P.R. China, Institute of Vibration Engineering Northwester Polytechnic University, Xi’an 710072, CHINA, 2003
2. ZHANG Yuping, JIANG Shouwei, ‘Review and Analysis of 3D-Model Reconstruction and Application’, Shanghai Jiaotong University, School of Mechanical and Dynamic Engineering,2003
3. DU Shouji, ‘Long Distance Box-bridge Jacking Research: Report of Starting Research’,Shanghai Jiaotong University, Shijiazhuang Municipal Traffic Project Provided a loan by World Bank, Contract number: TA3.1.4, 2003
4. ZHANG Wenqiang, YAN Heqing, HUANG Xuemei, WEI Bin, and WANG Chengtao, ‘3D Modeling and Rapid Prototyping for Dentition Defect Restoration’, School of Mechanical Engineering, Shanghai JiaoTong University, Shanghai 200030, China, The Ninth People’s Hospital, Attached to Shanghai Second Medical University, Shanghai 200011, China, 2003
5. ZHANG YuPing, JIANG Shouwei, YIN Zhongwei, ‘A Generic Approach for Leather Nesting with an Heuristic Simulated Annealing Based Genetic Algorith’, Shanghai JiaoTong University School of Mechanical & Dynamic Engineering, Huashan Road 1954, Shanghai,200030, 2003
6. HU Xin, XI JunTong, JIN Ye, ‘Shape analysis and parameterized modeling of a hip joint’,Institute of Computer Integrated Manufacturing, Shanghai Jiaotong University, Post Code 200030, Shanghai, China, 2003
7. LI LiJun, JIN XianLong, LI YuanYin, WEI JinQiao, WU WeiWei, ‘A Parallel Solver for Structural Modal Analysis’, High Performance Computing Center, Shanghai JiaoTong University, Republic of China, 2003
8. CHEN YongGuo, TIAN ZiPing, MIAO ZhengQing, ‘Application of Time-frequency Analysis to Fluidization Regimes Recognition in Circulating Fluidized Beds’, School of Mechanical and Power Engineering, Shanghai Jiao Tong University, Dongchuan Road 800,Shanghai, P.R. China, 200240, 2003
9. REN ShaoYun, ZHANG JianWu, GAO ChangYun, ‘Modeling and Simulation Analysis of the Torsional Vibration of a FR Driveline System’, School of Mechanical Engineering,Shanghai Jiaotong University, 2003
10. WU LiJun, CHEN HuiEr, ‘Mathematical Model for on-line Prediction of Bottom and Hearth of Blast Furnace by Particular Solution Boundary Element Method’, Department of Power and Mechanical Engineering, Shanghai JiaoTong University, Shanghai, 200030, People’s Republic of China, 2003.
11. JING XueDong, ZHANG GuoQing, PU GengQiang, WANG ChengTao, XU BinShi, ZHU Sheng, ‘Study on Architecture of Remanufacturing System’, School of Mechanical Engineering, Shanghai Jiao Tong University, Shanghai, China, Surface Engineering Institute, Beijing, China
12. CAI XiaoPing, JIN Chen, WU JunBiao, HIRAOKA Sumito, CHEN ShaoLin, ‘Application of Partial Singular Value Decomposition Analysis to Location of Vibration Sources of Elevator’,1. State Key Laboratory of Vibration, Shock & Noise, Shanghai Jiao Tong University, Shanghai, China, 200030, 2. Fujitec CO., Ltd. Osaka, 547-8510, Japan, 2003
13. YE Yao, LIAN ZhiWei, HOU ZhiJian, ‘Heat Exchange Analysis of Cooling Coils Based on a Dynamic Model’, Institute of Refrigeration and Cryogenics, Shanghai Jiao Tong University,Shanghai, 200030, China
14. NIU XinWen, DIN Han, XIONG YouLun, ‘Assembly Plans Generation Based on Precedence Graphs’, School of Mechanical Engineering, Shanghai JiaoTong University, Shanghai

APPENDIX B

]]>