JDBC MySQL connection - );sse's Personal Open Mind Pages

JDBC Mysql Connection for use with JSP/Servlets

Because I didn't understand much of Java yet and wanted to write a JSP/Servlet forum with a Mysql database behind it, I needed to write some code to make my forum connect to this database. I used the Mysql Connector/J (http://www.mysql.com/downloads/api-jdbc.html) to do this. The following code tests if the connector is available, if it is able to connect to the MySQL database and returns data from a query. Help for installing and configuring the connector and MySQL itself is available on the website mentioned above.

I used the code below to start writing the Jajaf forum. You're free to use the code. The only thing you have to do for the code to be usefull is a working Connector/J and change the strings "connectString" and "query" into your own. You probably want to change the name of the class itself ofcourse.


import java.io.* ;
import javax.servlet.* ;
import javax.servlet.http.* ;
import java.sql.* ;

public class connectMysql extends HttpServlet {
    private static Connection con;
    private String mysqlHost="localhost", mysqlDatabase="database",
mysqlUser="user", mysqlPassword="password";
    private String connectString = "jdbc:mysql://" + mysqlHost+"/" +
mysqlDatabase+"?user=" + mysqlUser+"&password=" + mysqlPassword;
    private String query = "SELECT * FROM tablex";
    
    public void doGet(HttpServletRequest req, HttpServletResponse res) throws
ServletException,IOException {
        Statement stmt = null;
        ResultSet rs = null;
        String[] results = null;
        PrintWriter out = res.getWriter();
        res.setContentType("text/html");
        out.println("<HTML><BODY><H1>testing</H1>" +
"<BR>URL to connect to MySQL via JDBC driver = " + connectString +
"<P>");
        
        try { 
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            out.println("Loading JDBC driver... OK<BR>");
        }
	catch(Exception e) { 
            out.println(e.getMessage()+"<BR>");
        };
        
        try {
            con = DriverManager.getConnection(connectString);
            out.println("Creating a new Connection with our new driver...
OK<BR>");
            try {
                try { 
                    stmt = con.createStatement();
                    out.println("Creating new Statement from Connection...
OK<BR>");
                }
                catch (SQLException sqlEx) { 
                    out.println("SQLException: " + sqlEx.getMessage()); 
                    out.println("SQLState: " + sqlEx.getSQLState()); 
                    out.println("VendorError: " + sqlEx.getErrorCode());
                };
                try { 
                    rs = stmt.executeQuery(query);
                    out.println("Executing query-Statement and putting into
Resultset... OK<BR>");
                   
out.println("&nbsp;<I>"+query+"</I><BR>");
                }
                catch (SQLException sqlEx) { 
                    out.println("SQLException: " + sqlEx.getMessage()); 
                    out.println("SQLState: " + sqlEx.getSQLState()); 
                    out.println("VendorError: " + sqlEx.getErrorCode());
                };

                out.println("Query data:<BR>");
                ResultSetMetaData rsmd = rs.getMetaData();
                int numcols = rsmd.getColumnCount();
                int numrow = 1;
                while(rs.next())
                {
                    for (int i = 1 ; i <= numcols; i++) {
                        out.println(numrow+":
|&nbsp;<I>\""+rs.getString(i)+"\"</I>&nbsp;|");
                    }
                    out.println("<BR>");
                    numrow++;
                }
            }
            finally {
                if (rs != null) { 
                    out.println("Closing ResultSet... OK<BR>");
                    try { rs.close(); } 
                    catch (SQLException sqlEx) { 
                        out.println("SQLException: " + sqlEx.getMessage()); 
                        out.println("SQLState: " + sqlEx.getSQLState()); 
                        out.println("VendorError: " + sqlEx.getErrorCode());    
                  
                    } 
                    rs = null; 
                }
                if (stmt != null) {
                    out.println("Closing Statement... OK<BR>");
                    try { stmt.close(); } 
                    catch (SQLException sqlEx) { 
                        out.println("SQLException: " + sqlEx.getMessage()); 
                        out.println("SQLState: " + sqlEx.getSQLState()); 
                        out.println("VendorError: " + sqlEx.getErrorCode());    
               
                    } 
                    stmt = null; 
                }
                


                // now we close the connection again
                try { 
                    out.println("Closing Connection... OK<BR>");
                    con.close();
                }
                catch(SQLException sqlEx) { 
                    out.println("SQLException: " + sqlEx.getMessage()); 
                    out.println("SQLState: " + sqlEx.getSQLState()); 
                    out.println("VendorError: " + sqlEx.getErrorCode());
                };  
            }
        }      
        catch(Exception e) { 
            out.println(e.getMessage()+"<P>");
        };
        out.println("</BODY></HTML>");
    }
    
    public void doPost(HttpServletRequest req, HttpServletResponse res) throws
ServletException,IOException {
        doGet(req,res);
    }
}