DB处理

DB处理

1.   获得连接：

public static Connection getConn() throws NamingException, SQLException {

        Context initCtx = new InitialContext();

        Context ctx = (Context) initCtx.lookup("java:comp/env");

        Object obj = (Object) ctx.lookup("jdbc/fee_db");

        javax.sql.DataSource ds = (javax.sql.DataSource) obj;

        Connection conn = ds.getConnection();

        conn.setAutoCommit(false);

 

        return conn;

    }

通过Jndi访问数据库的

2.   关闭连接：

public static void closeDB(Connection conn, Statement st, ResultSet rs) {

        try {

            if (rs != null)

                rs.close();

            if (st != null)

                st.close();

            if (conn != null)

                conn.close();

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

保存在DBUtil类中。

 

3.   执行语句：

3.1       PreparedStatement

 

public boolean checkLogin(String user_name, String password, Connection conn)

            throws Exception {

        PreparedStatement pst = null;  //a

        StringBuffer sql = new StringBuffer(

                "select * from t_user u where u.user_name=? and u.pwd=? and u.emp_id is not null");   //b

 

        boolean rtn = false;

        ResultSet rst = null;  //c

        pst = conn.prepareStatement(sql.toString());  //d

        pst.setString(1, user_name);

        pst.setString(2, Encrypter.encrypt(password));  //e

        rst = pst.executeQuery();  //f

        if (rst.next()) {

            rtn = true;

        }  //g

 

        DBUtil.closeDB(pst, rst);

        return rtn;

    }

0.打开连接

a.       声明PreparedStatement

b.       准备带?的SQL语句

c.       准备返回的结果集

d.       SQL传入PreparedStatement

e.       设置传入的参数

f.        执行SQL

g.       取结果集

0.  关闭连接

 

3.1   更新

 

 

4.   tomcat的配置

D:/Tomcat5.0/conf/Catalina/localhost/fee_pro.xml的配置：

<?xml version='1.0' encoding='utf-8'?>

<Context crossContext="true" workDir="work/Catalina/localhost/feepro" path="/feepro" debug="5" docBase="F:/JavaWork/feepro/web">

  <Logger className="org.apache.catalina.logger.FileLogger" suffix=".txt" prefix="localhost_feepro_log." timestamp="true"/>

  <Resource type="javax.sql.DataSource" auth="Container" name="jdbc/fee_db"/>

  <ResourceParams name="jdbc/fee_db">

   <parameter>

      <name>factory</name>

      <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>

    </parameter>

    <parameter>

      <name>maxActive</name>

      <value>100</value>

    </parameter>

    <parameter>

      <name>maxWait</name>

      <value>10000</value>

    </parameter>

    <parameter>

      <name>password</name>

      <value>wwmmbb</value>

    </parameter>

    <parameter>

      <name>url</name>

      <value>jdbc:mysql://192.168.1.100:3308/fee_db</value>

    </parameter>

    <parameter>

      <name>driverClassName</name>

      <value>com.mysql.jdbc.Driver</value>

    </parameter>

    <parameter>

      <name>maxIdle</name>

      <value>30</value>

    </parameter>

    <parameter>

      <name>username</name>

      <value>root</value>

    </parameter>

  </ResourceParams>

</Context>