[DAO] The DAO data layer code based on apache. commons. dbcp, commonsdbcp

[DAO] The DAO data layer code based on apache. commons. dbcp, commonsdbcp
**************************************** ********************************* *** Original article: blog.csdn.net/clark_xu Xu changliang's column**************************************** ********************************

DAO code is mainly based on the dept (Department List) emp (employee information list) of scott, the default oracle user. The connection pool connecting JDBC to oracle is implemented using apache. commons. dbcp.

There are four types of projects: the entity class entery. Dept object stores a row in the scott. dept table. The utils. DButils class creates a jdbc connection pool. DAO. DeptDao provides SQL statements for Dept operations. Index. index is the test class of the main function.

As shown above, buildpath is required to import jar:

(1) apache. commons class: commons-collections4-4.0.jar commons-pool-1.6.jar commons-dbcps-1.4.jar

(2) oracle. jdbc class: class12.jar

 

Step 1: Create an instance class entry. Dept ()

The Dept table has three fields: deptno Department number, dname Department name, AND loc location.

package entry;public class Dept {private  int deptno;private   String dname;private   String loc;@Overridepublic String toString() {return deptno+" "+dname+" "+loc;}public int getDeptno() {return deptno;}public void setDeptno(int deptno) {this.deptno = deptno;}public String getDname() {return dname;}public void setDname(String dname) {this.dname = dname;}public String getLoc() {return loc;}public void setLoc(String loc) {this.loc = loc;}}

Step 2: create a connection class for the oracle database.

Use the Properties file to save the url, user, password, and driver name of the oracle database

The content of the file whose name is property is:

Driver = oracle. jdbc. OracleDriver
Url = jdbc: oracle: thin: @ 127.0.0.1: 1521: rundb
User = scott
Password = tiger

The Code is as follows: Read the url, user, password from the file

Create an openConneciton method to create a jdbc connection pool. Use the apache. commons. dbcp. BasicDataSources object.

Package utils; import java. io. IOException; import java. SQL. connection; import java. SQL. SQLException; import java. util. properties; import org. apache. commons. dbcp. basicDataSource; public class DButils {private static String url; private static String driver; private static String user; private static String password; static {Properties props = new Properties (); try {// load the file props from the class path. load (DButils. class. getClassLoader (). getResourceAsStream ("utils/property");} catch (IOException e1) {// TODO Auto-generated catch blockSystem. out. println ("Password File not found"); e1.printStackTrace ();} user = props. getProperty ("user"); url = props. getProperty ("url"); driver = props. getProperty ("driver"); password = props. getProperty ("password");}/*** Class Method * @ return Conneciton * @ throws SQLException */public static Connection openConnection () throws SQLException {BasicDataSource ds = new BasicDataSource (); ds. setDriverClassName (DButils. driver); ds. setUrl (DButils. url); ds. setUsername (DButils. user); ds. setPassword (DButils. password); // initialize the ds format. setInitialSize (10); // maximum number of idle connections ds. setMaxIdle (50); ds. setMaxActive (50); Connection con = ds. getConnection (); return con ;}}

Step 4: Create a DAO class. Create different methods to implement SQL statement operations on the database, and use the List <Dept> object to save all records of the table. Each Dept object stores a row in the dept table.

Define a findAll () method to query the content of a dept table.

Package dbo; import entry. dept; import utils. DButils; import java. SQL. connection; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. SQLException; import java. util. arrayList; import java. util. list; public class DeptDAO {private static final String FIND_ALL = "select deptno, dname, loc from dept"; public List <Dept> findAll () throws SQLException {Connection con = null; con = DButils. openConnection (); PreparedStatement stmt = con. prepareStatement (FIND_ALL); ResultSet rs1_stmt.exe cuteQuery (); // defines the set List <Dept> deptlist = new ArrayList <Dept> (); Dept d = null; while (rs. next () {int deptno = rs. getInt ("deptno"); String dname = rs. getString ("dname"); String loc = rs. getString ("loc"); // deptlist. add (toDept (rs); d = new Dept (); d. setDeptno (deptno); d. setDname (dname); d. setLoc (loc); System. out. println (d. toString (); // System. out. println (mydept. toString (); // Add it to the deptlist set. add (d); System. out. println (deptlist. toString ();} return deptlist ;}}

Step 5: Create the main test code

package index;import java.sql.SQLException;import java.util.List;import dbo.DeptDAO;import entry.Dept;public class index {public static void main(String[] args) throws SQLException {DeptDAO deptdao=new DeptDAO();List<Dept> deptlist=deptdao.findAll();//System.out.println(deptlist.toString());/*for(int i=0;i<deptlist.size();i++){Dept d = deptlist.get(i);System.out.println(d.toString());}*/}}

Step 6: use the main function to test the code, which is inconvenient. We recommend that you use JUnit for unit testing. Create the JUnit test class DeptDAOTest. java

Package index; import java. SQL. SQLException; import java. util. list; import org. junit. test; import dbo. deptDAO; import entry. dept; public class DeptDAOTest {/*** test the findAll Method * @ throws SQLException */@ Testpublic void testfindAll () throws SQLException {DeptDAO deptdao = new DeptDAO (); list <Dept> deptlist = deptdao. findAll (); for (Dept o: deptlist) {System. out. println ("Department No.:" + o. getDeptno () + "department name:" + o. getDname () + "Location:" + o. getLoc ());}}}