integrate ms access and mysql in java

Question

Hi,

I have a problem connecting to MS Access and MySQL using Java. My problem is that I cannot find the driver for MySQL. Here is my code:

<%@ page import="java.sql.*" %>

<%
   Connection odbcconn = null;
   Connection jdbcconn = null;
   PreparedStatement readsms = null;
   PreparedStatement updsms = null;
   ResultSet rsread = null;

   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  //load database driver
   odbcconn = DriverManager.getConnection("jdbc:odbc:SMS");  //connect to database
   readsms = odbcconn.prepareStatement("select * from inbox where Status='New'");
   rsread = readsms.executeQuery();
   while(rsread.next()){
        Class.forName("com.mysql.jdbc.Driver");
        jdbcconn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bakme", "root", "");  //connect to database
        updsms = jdbcconn.prepareStatement("insert into inbox(sms,phone) values (?,?)");
        updsms.setString(1, rsread.getString("Message"));
        updsms.setString(2, rsread.getString("Phone"));
        updsms.executeUpdate();
   }

%>

Answer 1

Thus, you get a ClassNotFoundException on the MySQL JDBC driver class? Then you need to put the MySQL JDBC driver JAR file containing that class in the classpath. In case of a JSP/Servlet application, the classpath covers under each the webapplication's /WEB-INF/lib folder. Just drop the JAR file in there. Its JDBC driver is also known as Connector/J. You can download it here.

That said, that's really not the way how to use JDBC and JSP together. This doesn't belong in a JSP file. You should be doing this in a real Java class. The JDBC code should also be more robust written, now it's leaking resources.

Answer 2

BalusC is spot on: this is not the way you should write something like this.

Connection, Statement, and ResultSet all represent finite resources. They are not like memory allocation; the garbage collector does not clean them up. You have to do that in your code, like this:

// Inside a method
Connection connection = null;
Statement statement = null;
ResultSet rs = null;

try
{
    // interact with the database using connection, statement, and rs
}
finally
{
    // clean up resources in a finally block using static methods, 
    // called in reverse order of creation, that don't throw exceptions
    close(rs);
    close(statement);
    close(connection);
}

But if you do decide to move this to a server-side component, you're bound to have huge problems with code like this:

while(rsread.next())
{
    Class.forName("com.mysql.jdbc.Driver");
    jdbcconn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bakme", "root", "");  //connect to database
    updsms = jdbcconn.prepareStatement("insert into inbox(sms,phone) values (?,?)");
    updsms.setString(1, rsread.getString("Message"));
    updsms.setString(2, rsread.getString("Phone"));
    updsms.executeUpdate();
}

Registering the driver, creating a connection, without closing it, and repeatedly preparing a statement inside a loop for every row that you get out of Access shows a serious misunderstanding of relational databases and JDBC.

You should register the driver and create connections once, do what needs to be done, and clean up all the resources you've used.

If you absolutely MUST do this in a JSP, I'd recommend using JNDI data sources for both databases so you don't have to set up connections inside the page. You should not be writing scriptlet code - better to learn JSTL and use its <sql> tags.

Answer 3

You can use this link to download the MySql Driver. Once you download it, you need to make sure it is on the class path for the web server that you are using. The particulars of configuring JDBC drivers for a server vary from server to server. You may want to edit your question to include more details to get a better answer.