Configuration method of connecting access database in Java Web project

Time:2021-2-23

The teacher decided to use the access database in the final exam to add, delete, modify and query. I think I have no problem now, but I used to connect the access database in the JSP page. No matter which way I do, I have done the connection exercises. But now I want my project’s Java code to access the access database to encapsulate in Dao and connect to the database in Dao, It has nothing to do with the servlet API. Most people will give priority to using ODBC data source or absolute path to connect to access database, but I personally think that it is not good to do so. If this way is adopted, after the project is completed, it can not run on other people’s server, because the database information does not exist, and my current idea is no matter which project is put Each server supporting JDBC ODBC machine can connect to the database and run the project. Therefore, to complete this operation, the only way is to let the access database’s mdb file move with the movement of the project. Therefore, in a Java Web project, put the mdb file into Webroot or its subdirectory. But how to get the real path of the MDB in Dao?

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

Let’s review the use of access database in JSP first!

For example, there are the following access database student, table basic, and 6 records. Now their data are displayed in JSP in several ways. As shown in the figure:

For several ways to connect to access database, they are basically based on jdbc-odbc, of course, there are pure jdbc driver ways. I won’t talk about it for the moment. For these methods, the code is the same except for the connection. So here first write several ways to get the connection, and then use the complete code to display.

Mode 1: direct connection through jdbc-odbc bridge:

1. For this method, first of all, we need to establish an ODBC data source. My system is win7 system, so select “control panel – management tools – data source (ODBC)” in turn to open the data source manager, as shown in the figure

2. In the “system DSN” tab, click the “add” button to open the create data source dialog box, and select the access database driver “Microsoft Access driver *. MDB”, as shown in the figure:

3. Click Finish to open the following dialog box, enter the name of the data source “jdbc-odbc” in the data source name, and click Select to select the database to operate“ student.mdb ”Click OK to complete the data source configuration. As shown in the figure:

4. After 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 to connect access database through jdbc-odbc. This way of connection can be very convenient to remember the connection URL code, which is very useful. The following code is written normally.

Method 2: connect through the absolute path of the database

As mentioned above, several methods here are based on jdbc-odbc. So load driven Class.forName The parameters in () are“ sun.jdbc.odbc .JdbcOdbcDriver”。 In this way, I will student.mdb The file is placed in the root directory of disk E. when using it, write the absolute path of the database directly. 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)};DBQ=”+”e://student.mdb “;
Connection con = DriverManager.getConnection(url);

For this way, you don’t need to configure the data source. Although there are many codes, it’s easy to understand. It’s also very common.

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

For this way, I personally think it is very suitable to use in Java Web applications. If you give a good application to others, others can also use it. I put the database file in the root path of the web application. Then the code to dynamically obtain the 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);

These are three ways to get a connection. Next is the code displayed. 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 number < / th >
< th > name < / th >
< th > age < / th >
< th > address < / th >
< th > Chinese
< th > mathematics
< th > English
</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>
</html>

The result of running JSP is as follows:

When the connection is changed to the second method, it is easy to use. 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 > < head > < title > the second way for JDBC to connect to access database < th > Chinese < / th > < th > mathematics < / th > < th > English < / th > < / TR > <% Class.forName (" sun.jdbc.odbc .JdbcOdbcDriver"); String url = " jdbc:odbc :driver={Microsoft Access Driver (*.mdb)};DBQ="+"e:// student.mdb  "; 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></html>

The result of running JSP is as follows:

When the connection is changed to the third method, it is still easy to use. 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 > < head > < title > the third way for JDBC to connect to access database < th > Chinese < / th > < th > mathematics < / th > < th > English < / th > < / TR > <% 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); 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></html>

The result of running JSP is as follows:

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

The third way is the way to use now. You can access the database in the Java class.
First, put the mdb file under Webroot, and when you click the hyperlink, transfer 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 this way, the Dao can find the path of the database file according to the passed path. And successfully connected to the database, to achieve the hierarchical concept.
But there is a problem. If you put it under Webroot and other people know the location of the database, you can access it?
For this reason, I have been thinking for a long time. Since I am accessing the database in 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, so it can’t be accessed, so my URL code becomes like this:
String url = “jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=student.mdb “;
But it failed, and the file was not found. Later, it was easy to put the file under “project”. But once it was given to others, it would not work. So it had to be put under Src. Only in this way can it be put under Webroot or webcontent, under classes, accessed by Java classes, given to others, and the project can run. But how to find the database file in the classes directory. I have been looking for a method until I find the following method:
Method 1: the getresource () method of class or the getresource () method of classloader class

Method 2: getpath () method of URL class
Find the directory of the current class through the getresource () method of the class. Here, the Dao class is in the com.student.dao Package, then the method returns the path of the class. If the parameter is’ / ‘, it returns the root directory of the classes. In this way, you can get the path of the database mdb file under the classes. Then get the real path of the string type file path on the server through the getpath () method of the URL class.
The code is as follows:
String path = this.getClass (). Getresource (“/”). Getpath (). Replaceall (“% 20”, “); / / replaceall method is to solve the problem of space character in path
path = path.substring (1, path.length ()); / / intercept the path, and an extra bar will be added in front of the obtained path Class.forName (” sun.jdbc.odbc .JdbcOdbcDriver”);
String url = “jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=”+path+”student.mdb”;
con = DriverManager.getConnection(url);
Encapsulate the above code in Dao, and you can access the database. You can use access database to realize MVC design pattern in Java Web project, and add Dao and VO. It is a way to use servlet API to transfer the path of the mdb file to Dao, and it is also a way to use DAO to encapsulate access. Sometimes, for convenience, you can put the mdb file under Webroot and transfer the path of the database file to Dao through JSP or servlet. I think the access database is better, you can move the database and the project together. These two ways of customs clearance can be run on any machine.
At present, all I know is to do so, and I think it is a better way. It is a good way to connect the database with Java language and put the database file in the same directory as the class path. If there are other ways, I hope you can come up with them.

Recommended Today

Deeply analyze the principle and practice of RSA key

1、 Preface After experiencing many dark moments in life, when you read this article, you will regret and even be angry: why didn’t you write this article earlier?! Your darkest moments include: 1. Your project needs to be connected with the bank, and the other party needs you to provide an encryption certificate. You have […]