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 was published yesterday, but later after some verification a bit of a problem, so today changed a re-release
The teacher decided to use the Access database for the final exam to implement additions and deletions, I think now I have no problem, but used to be in the JSP page connected to the Access database, whether it is the following way are connected to the practice, But now I want to get the Java code in my project that accesses the Access database, encapsulating it in DAO, connecting to the database in DAO and not having any relationship with the Servlet API. For most people it is preferable to use an ODBC data source or to connect to an Access database using an absolute path, but I personally think that this is not very good, if the project is done in this way, put on the other person's server is not able to run, because the database information does not exist, And my idea now is that you can connect to the database and run the project on a server that supports the JDBC-ODBC machine, so you do this only if the MDB file for the Access database moves as the project moves, so in the Java Web project, Put the MDB file under Webroot or its subdirectories. But how do you get the true path of the MDB in DAO?
In fact, this and in the JSP dynamic to get the MDB file path of the idea is basically 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. ：
For several ways to connect to an Access database, it is basically based on the JDBC-ODBC approach and, of course, the pure JDBC-driven approach. I won't say it for the time being. In these ways, the other code is the same except where the connection is made. So here are some ways to get a connection and then display it in full code.
Mode one: Through the Jdbc-odbc Way Bridge connects directly:
1, for this 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:
2. On the System DSN tab, click the Add button to open the Create Data Source dialog box and select the driver for the Access database, Microsoft access Driver (*.mdb):
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 to operate "Student.mdb", click the OK button to complete the configuration of the data source. ：
4, the data source is configured, you can write the code to get the connection, 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 are the key codes for connecting to an Access database through JDBC-ODBC. This way the connection can be very convenient to remember the URL code of the connection, which is very useful. 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 load-driven class.forname () are all "Sun.jdbc.odbc.JdbcOdbcDriver". For this way I put the Student.mdb file in the root of the E-disk, in use, the direct write on the absolute path of the database is OK. The code to get 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)};D bq=" + "E://student.mdb";
Connection con = drivermanager.getconnection (URL);
There is no need to configure the data source for this way, although the code is more, but well understood. is also very common.
method Three: Obtain the absolute path connection of the database by request
In this way, I personally think that it is very suitable for use in Java Web Applications, will be well applied to others, others can also use. I put the database file under the root path of the web App. The code to get the connection dynamically 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)};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 this:
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>
The results of running the JSP are as follows:
When the connection is changed to the second way, the JSP code looks like this:
The results of running the JSP are as follows:
When the connection is changed to a third way, the JSP code looks something like this:
The results of running the JSP are as follows:
The directory structure for my project in this way is as follows:
The third way is the way to use it now, in the Java class, to access the database. First, put the MDB file under Webroot, and when you click the hyperlink, pass the message 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 you can find the path to the database file based on the path passed. and successfully connected to the database, the realization of a hierarchical concept. But there is a problem, if placed under the Webroot, others know the location of the database, can not access it? I think for a long time, since it is in the Java class to access the database, then the MDB file can be placed below SRC, the answer is yes. So in MyEclipse or eclipse, the file is copied to the classes root path under the Web-inf of the Web project, and it is not accessible, so my URL code becomes this: String url = "jdbc:odbc:driver={ Microsoft Access Driver (*.mdb)};D Bq=student.mdb "; But the result failed, did not find the file, but later put the file under the" project ", it is good, but so once to others, it is not, so you have to put under the SRC, Only in this way will put to Webroot or webcontent under, will put to classes under, can be accessed by Java class, can give others project can run. But how can I find the database file under the classes directory? have been looking for a method until you find the following method: Method One: The Class GetResource () method or the ClassLoader class
Method Two: The GetPath () method of the URL class finds the directory 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, if the parameter is "/", The root of the classes is returned, so you can get the path to 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 this: 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 have access to the database. You can use an Access database in your Java Web project to implement the MVC design pattern and add DAO and VO. For using the Servlet API to pass the path of an MDB file to DAO is a way to encapsulate access using DAO, and sometimes it is convenient to put the MDB file under Webroot and pass the path of the database file to DAO through a JSP or a servlet. I feel that the Access database is better, and you can move the database and the project together. The two ways of clearance can be run on any machine. At present, all I know is this, and I think it is a better practice. It is good practice to connect to the database in the Java language and place the database files in the same directory as the classpath. If 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 was published yesterday, but later after some verification a bit of a problem, so today changed a re-release
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
How to connect an Access database in a Java Web project
