How to connect an Access database in a Java Web project

This article is an upgrade to the "JDBC Connection to access database in several ways" of the previous days. I decided to write this blog because of the problems I encountered when I was doing some small projects. The blog has been published yesterday. But then after some verification a bit of a problem, so today changed a bit again announced

The teacher decided to use the Access database to make additions and deletions to the final exam. I think I have no problem now. But I used to connect to an Access database in a JSP page, either way, but now I want to get access to the Java code in my project, which is encapsulated in DAO, connected to a database in DAO, not and servlet The API has no matter what the relationship.

For most people who prefer to use an ODBC data source or to connect to an Access database using an absolute path, I personally feel that this is not a good thing to do, assuming this is the way the project is done. It cannot be performed on someone else's server because the database information does not exist. And my idea now is that the database can be connected regardless of the project on the server that supports the JDBC-ODBC machine. and execute the project. So to finish this operation, just have the MDB file in the Access database move as the project moves, so in the Java Web project. Put the MDB file under Webroot or under its subfolders. But how do you get the true path of the MDB in DAO?

In fact, the idea of getting the MDB file path dynamically in JSP is essentially the same.

Let's review the JSP using the Access database!

For example, the following Access database student, table Basic, and 6 records now display their data in a JSP in several ways. What you see:

For several ways to connect to an Access database. are basically based on the JDBC-ODBC approach. There are, of course, pure jdbc-driven approaches. I'm not going to say a word here.

For these kinds of ways. In addition to the place to get the connection is different. The rest of the code is the same. So here are some ways to get a connection first. It is then displayed with the full code.

Mode one: Through the Jdbc-odbc Way Bridge connects directly:

1, for such a way, first to establish an ODBC data source, my system is Win7 system, so Select "Control Panel----Management tool----data source (ODBC)", open the data source Manager, see:

2. On the System DSN tab, click the Join button to open the Create Data Source dialog box. Select the driver for the Access database that Microsoft access Driver (*.mdb) sees:

3. Click Finish button. such as the following dialog box appears. In the data source name, enter the name of the data source "Jdbc-odbc", click Select button, select the database "Student.mdb" you want to manipulate, and click OK to configure the button to complete the data source. What you see:

4, the data source is configured, you can write the code to get the connection, such as the following see:

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 are the key codes for connecting to an Access database through JDBC-ODBC. This way the connection can be very convenient to the memory of the connection URL code, which is very practical.

The code that follows is written normally.

Mode two: Connect through the absolute path where the database resides

The above-mentioned methods are based on the JDBC-ODBC approach. So the parameters in the Class.forName () loaded drive are all "sun.jdbc.odbc.JdbcOdbcDriver". For this way I put the Student.mdb file in the root folder of the E-disk, when used. Write the absolute path to the database directly. Get the code for the connection as seen below:

Copy the Code code such as the following:
Class.forName ("Sun.jdbc.odbc.JdbcOdbcDriver");
String url = "Jdbc:odbc:driver={microsoft Access driver (*.mdb)};D bq=" + "E://student.mdb";
Connection con = drivermanager.getconnection (URL);


There is no need to configure the data source for such a way, although the code is much better understood. is also not used frequently.

method Three: Obtain the absolute path connection of the database by request

For such a way. I personally think it's a great fit to use in a Java web App. Apply the good to others, others can also use. I put the database file under the root path of the web App. Then dynamically get the code for the connection as seen below:

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)};D bq=" +path+ "Student.mdb";
Connection con = drivermanager.getconnection (URL);


Here are three ways to get connected. The next step is the code shown. The code looks like the following:

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" >
<title>Access</title>
<body>
<table border= "1" width= "40%" >
<tr bgcolor= "Gray" >
<th> Study No. </th>
<th> name </th>
<th> Age </th>
<th> Address </th>
<th> language </th>
<th> Math </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.executequery (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>

Execute the JSP results as seen below:

When you change the connection to another way, it works, and the JSP code looks like this:

Execute the JSP results as seen below:

It still works when the connection is changed to a third way. The JSP code looks like the following:

Execute the JSP results as seen below:

For such a way the folder structure of my project is seen in the following:

The third way is the way to use it today. Be able to access the database in the Java class. First put the MDB file below Webroot, when you click on the hyperlink. Pass 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 connection in the DAO's init () method: Class.forName ("Sun.jdbc.odbc.JdbcOdbcDriver");
String url = "Jdbc:odbc:driver={microsoft Access driver (*.mdb)};D bq=" +path+ "Student.mdb";
Connection con = drivermanager.getconnection (URL), so that in DAO it is possible to find the path to the database file based on the path passed.

and successfully connected to the database. The concept of layering is realized.

However, there is a problem, assuming that under the Webroot, others know the location of the database, not to be able to access the question? I've been thinking about this for a long time. Since you are visiting the database in the Java class. So can you put the MDB file under SRC, the answer is yes. This is in MyEclipse or eclipse. The file was copied to the classes root path under the Web-inf of the Web project, so I could not Access it, so my URL code changed to this: String URL = "Jdbc:odbc:driver={microsoft. Driver (* . mdb)};D Bq=student.mdb "; But the result failed and no files were found. However, the file is then placed under project. So that, but once to others, it is not, so it has to be placed under the SRC, only this will be placed under the Webroot or webcontent, will be placed under the classes, the ability to be interviewed by the Java class. Ability to give others a project can be carried out. But how can you find the database file under the Classes folder?

Always looking for a way to find a method such as the following: Method one: The Class GetResource () method or the ClassLoader class
Method Two: The GetPath () method of the URL class finds the folder where the current class is located through the class GetResource () method, where the DAO class is under the Com.student.dao package. Then the method returns the path of the class, assuming that the parameter is "/", the return is the root folder of classes, so that you can get the path of the database MDB file under classes.

The GetPath () method of the URL class is then used to get the true path of the file path of the string type on the server. The code looks like the following: String Path = This.getclass (). GetResource ("/"). GetPath (). ReplaceAll ("%20", "");// The ReplaceAll method is to resolve the problem that contains a space character in the path, Path = Path.substring (1,path.length ()),//intercept the path, and the path obtained will have a bar class.forname (" Sun.jdbc.odbc.JdbcOdbcDriver "); String url = "Jdbc:odbc:driver={microsoft Access driver (*.mdb)};D bq=" +path+ "student.mdb"; con = Drivermanager.getconnection (URL); If you encapsulate the above code in DAO, you will be able to access the database.

Can be in a Java Web project. Use an Access database to implement the MVC design pattern and join DAO and VO. It is a way to pass the path of the MDB file to DAO using the Servlet API. Using DAO to encapsulate access is also a way. Sometimes the MDB file can be placed under webroot for convenience. Pass the path of the database file to DAO through a JSP or a servlet.

I feel that the Access database is better than this. The ability to move databases and projects together. The two ways of customs clearance can be performed on any machine, no matter what.

Right now, all I know is this, and I think it's a better approach.

It is a good practice to connect to the database in the Java language and place the database files in the same folder as the classpath. Suppose there are other ways, I hope you will come forward. This article is an upgrade to the "JDBC Connection to access database in several ways" of the previous days.

I decided to write this blog because of the problems I encountered when I was doing some small projects. The blog has been published yesterday. But then after some verification a bit of a problem. So today we've changed the announcement again.

How to connect an Access database in a Java Web project