How to configure Access database connection in Java Web project

The teacher decided to use the access database for addition, deletion, modification, and query in the final examination. I think I have no problem now, but I used to connect to the access database on the JSP page, both of the following methods have been used for connection exercises, but now I want the java code for accessing the access database in my project to be encapsulated in DAO and connected to the database in DAO, it does not have any relationship with Servlet APIs. Most people prefer to use ODBC data sources or use absolute paths to connect to the access database. However, I personally think this is not very good, after the project is completed, it cannot be run on another person's server because the database information does not exist, my current idea is that no matter whether the project is placed on a server that supports the jdbc-odbc machine, you can connect to the database and run the project, so you need to complete this operation, only when the mdb file of the access database is moved with the project, in the Java Web project, the mdb file is placed under WebRoot or its subdirectory. But how can we get the actual path of the mdb in DAO?

In fact, this idea is basically the same as the idea of dynamically obtaining the path of the mdb File in JSP.

Let's review how to use the access database in jsp!

For example, the following Access database student, table basic, and six records are displayed in Jsp in several ways. :

For several ways to connect to the Access database, basically is based on the JDBC-ODBC, of course, there are pure JDBC driver. I will not talk about it for the moment. Except for the difference in the connection, other codes are the same. So here we will first write several methods to get the connection, and then use the complete code to display it.

Method 1: direct connection through the JDBC-ODBC Bridge:

1. For this method, you must first create an ODBC Data Source. My system is Windows 7, so select "Control Panel-management tools-Data Source (ODBC)" in sequence to open the data source Manager ,:

2. on the "system DSN" tab, click the "add" button to open the "Create data source" dialog box, and select the "Microsoft Access Driver (*. mdb)" Driver of the Access database )":

3. Click the finish button, the following dialog box appears, enter the data source name "JDBC-ODBC" in the data source name, click the select button, select the Database "student. mdb, click OK to complete the data source configuration. :

4. After the data source is configured, you can write the connection Code as follows:

Copy codeThe Code is as follows:
Class. forName ("sun. jdbc. odbc. JdbcOdbcDriver ");
String url = "jdbc: odbc: JDBC-ODBC ";
Connection con = DriverManager. getConnection (url );


The above three lines of code is the key code to connect to the Access database through the JDBC-ODBC. This method can easily remember the url code of the connection, which is very useful. The subsequent code is normally written.

Method 2: connect through the absolute path of the database

The methods mentioned above are based on the JDBC-ODBC method. Therefore, the parameters in the Class. forName () of the driver are "sun. jdbc. odbc. JdbcOdbcDriver ". In this way, I put the student. mdb file under the root directory of the E disk. When using the file, just write the absolute path of the database. The code for getting the connection is as follows:

Copy codeThe Code is as follows:
Class. forName ("sun. jdbc. odbc. JdbcOdbcDriver ");
String url = "jdbc: odbc: driver = {Microsoft Access Driver (*. mdb)}; DBQ =" + "e: // student. mdb ";
Connection con = DriverManager. getConnection (url );


You do not need to configure the data source for this method. Although there are many codes, it is easy to understand. It is also very common.

Method 3: Obtain the absolute path of the database through the request

For this method, I personally think it is suitable for Java Web applications and can be used by others. I put the database file under the root path of the Web application. The code for dynamically obtaining a connection is as follows:

Copy codeThe Code is as follows:
String path = application. getRealPath ("/index. jsp ");
Path = path. substring (0, path. lastIndexOf ("\") + "\\";
Class. forName ("sun. jdbc. odbc. JdbcOdbcDriver ");
String url = "jdbc: odbc: driver = {Microsoft Access Driver (*. mdb)}; DBQ =" + path + "student. mdb ";
Connection con = DriverManager. getConnection (url );


The preceding three methods are used to obtain the connection. The following is the displayed code. The Code is as follows:

Copy codeThe Code is as follows:
<% @ Page language = "java" import = "java. util. *, java. SQL. * "contentType =" text/html; charset = UTF-8 "pageEncoding =" UTF-8 "%>

<! DOCTYPE html PUBLIC "-// W3C // dtd html 4.01 Transitional // EN" "http://www.w3.org/TR/html4/loose.dtd">
<Html>
<Head>
<Title> Access </title>
</Head>
<Body>
<Table border = "1" width = "40%">
<Tr bgcolor = "gray">
<Th> Student ID </th>
<Th> name </th>
<Th> age </th>
<Th> address </th>
<Th> language </th>
<Th> mathematics </th>
<Th> English </th>
</Tr>
<%
Class. forName ("sun. jdbc. odbc. JdbcOdbcDriver ");
String url = "jdbc: odbc: JDBC-ODBC ";
Connection con = DriverManager. getConnection (url );
Statement st = con. createStatement ();
String SQL = "select * from basic ";
ResultSet rs = st.exe cuteQuery (SQL );
While (rs. next ())
{
%>
<Tr>
<Td> <% = rs. getString (1) %> </td>
<Td> <% = rs. getString (2) %> </td>
<Td> <% = rs. getInt (3) %> </td>
<Td> <% = rs. getString (4) %> </td>
<Td> <% = rs. getInt (5) %> </td>
<Td> <% = rs. getInt (6) %> </td>
<Td> <% = rs. getInt (7) %> </td>
</Tr>
<%
}
Rs. close ();
St. close ();
Con. close ();
%>
</Table>
</Body>
</Html>

The result of running JSP is as follows:

The JSP code is as follows:

<% @ Page language = "java" import = "java. util. *, java. SQL. * "contentType =" text/html; charset = UTF-8 "pageEncoding =" UTF-8 "%> <! DOCTYPE html PUBLIC "-// W3C // dtd html 4.01 Transitional // EN "" http://www.w3.org/TR/html4/loose.dtd "> <Html> 
The result of running JSP is as follows:


The JSP code is as follows:
<% @ Page language = "java" import = "java. util. *, java. SQL. * "contentType =" text/html; charset = UTF-8 "pageEncoding =" UTF-8 "%> <! DOCTYPE html PUBLIC "-// W3C // dtd html 4.01 Transitional // EN "" http://www.w3.org/TR/html4/loose.dtd "> <Html> 
The result of running JSP is as follows:


In this way, the directory structure of my project is as follows:

The third method is used now. You can access the database in the Java class. First, put the mdb file under WebRoot. When you click the hyperlink, send the information to the Servlet and write the following statement in the Servlet: String path = request. getServletContext (). getRealPath ("/");

Dao dao = new Dao ();

Dao. init (path );

Initialize the Connection in the init () method of dao: Class. forName ("sun. jdbc. odbc. JdbcOdbcDriver ");

String url = "jdbc: odbc: driver = {Microsoft Access Driver (*. mdb)}; DBQ =" + path + "student. mdb ";

Connection con = DriverManager. getConnection (url); In dao, you can find the path of the database file based on the passed path. And successfully connected to the database, achieving the hierarchical concept. However, there is a problem. If someone else knows the database location under WebRoot, won't they be able to access it? For this reason, I have been thinking for a long time. Since I access the database in the Java class, can I put the mdb file under src? The answer is yes. In this way, in MyEclipse or Eclipse, the file is copied to the classes root path under the WEB-INF of the Web Project, can not access, so my URL code becomes like this: string url = "jdbc: odbc: driver = {Microsoft Access Driver (*. mdb)}; DBQ = student. mdb "; but the result failed and the file was not found, but it was easy to put the file under the" project ", but once it was given to others, it would not work, therefore, you have to put it under src. Only in this way can you put it under WebRoot or WebContent and put it under classes to be accessed by java classes, so that other projects can be run. But how can we find the database files under the classes directory. Always find a method until the following method is found: Method 1: getResource () method of the Class or

Method 2: The getPath () method of the URL Class uses the getResource () method of the Class to find the directory of the current Class. Here, the dao Class is in com. student. under the dao package, this method returns the path of the class. If the parameter is "/", the return is the root directory of classes, in this way, you can obtain the path of the database mdb file under the classes. Then, use the getPath () method of the URL class to obtain the actual path of the file path of the string type on the server. The Code is as follows: String path = this. getClass (). getResource ("/"). getPath (). replaceAll ("% 20", ""); // The replaceAll method is used to solve the problem that the path contains space characters. path = path. substring (1, path. length (); // truncate the path and add a Class to the front of the obtained path. forName ("sun. jdbc. odbc. jdbcOdbcDriver "); String url =" jdbc: odbc: driver = {Microsoft Access Driver (*. mdb)}; DBQ = "+ path +" student. mdb "; con = DriverManager. getConnection (url); encapsulate the preceding code in DAO to access the database. In the Java Web project, you can use the Access database to implement the MVC design mode and add DAO and VO. You can use Servlet APIs to pass the path of the mdb file to dao. Using dao to encapsulate Access is also a way. Sometimes you can put the mdb file under WebRoot for convenience, use JSP or Servlet to pass the path of the database file to dao. I think the Access database is better. You can move the database and the project together. These two methods can be run on any machine. At present, all I know is to do this. I also think this is a good practice. It is a good practice to connect to the database in Java and put the database files in the same directory as the class path. If there are other methods, I hope you can come up with them.