This is the DAO class which consists of the JDBC database connection
package ca.sheridancollege.dao;
import ca.sheridancollege.beans.*;
import java.util.ArrayList;
import java.sql.*;
public class DAO {
public String user;
public String password;
public String host;
public String database = "homework22";
public String table = "student";
public DAO(String host, String user, String password) {
this.user = user;
this.password = password;
this.host = host;
}
public void addContact(Student student) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con;
//Try to connect to the database. If the database does
//not exist then create it.
//The database in this case is called "homework22"
try {
con = DriverManager.getConnection(host + database, user, password);
} catch (Exception e) {
con = DriverManager.getConnection(host + database, user, password);
Statement st = con.createStatement();
st.executeUpdate("CREATE DATABASE " + database + ";");
st.executeUpdate("USE " + database + ";");
con = DriverManager.getConnection(host + database, user, password);
}
String add = "INSERT INTO " + table + " VALUES "
+ "('" + student.getName()
+ "', '" + student.getId()
+ "', '" + student.getGrade()
+ "');";
Statement st = con.createStatement();
//Add the new contact into the SQL table.
try {
st.executeUpdate(add);
} catch (Exception e) {
System.out.println(e);
String make = "CREATE TABLE " + table
+ "( Name VARCHAR(50) "
+ ", " + " Id VARCHAR(20) "
+ ", " + " Grade VARCHAR(10) "
+ ");";
st.executeUpdate(make);
st.executeUpdate(add);
}
con.close();
} catch (Exception e) {
System.out.println(e);
}
}
public ArrayList<Student> getContacts() {
ArrayList<Student> contacts = new ArrayList();
try {
//Create a connection to our JDBC Driver
Class.forName("com.mysql.jdbc.Driver");
//Create a connection to the SQL Server
//(Database, user, password)
Connection con = DriverManager.getConnection(host + database, user, password);
Statement st = con.createStatement();
String Query = "SELECT * FROM " + table + ";"; //MySQL statement
ResultSet rs = st.executeQuery(Query);
//ResultSet rs = st.executeUpdate(Query);
ResultSetMetaData rsmd = rs.getMetaData();
int numColumns = rsmd.getColumnCount();
while (rs.next()) {
Student c = new Student(
rs.getString(1),
rs.getInt(2),
rs.getDouble(3)
);
// System.out.println(d.toString());
contacts.add(c);
}
// so the fact is that the vlue
con.close();
} catch (Exception e) {
System.out.println(e);
}
return contacts;
}
}
This is the code which gets the username, password and the database connectivity for the JDBC.
The homework22 database if initially not created will be created using this code.
The user enters details in the index.html page and there is a view link which displays the result in a table.
But show an exception
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'homework22'
This is the web.xml file
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
version="3.1">
<context-param>
<param-name>host</param-name>
<param-value>jdbc:mysql://localhost/</param-value>
</context-param>
<context-param>
<param-name>user</param-name>
<param-value>root</param-value>
</context-param>
<context-param>
<param-name>password</param-name>
<param-value>root</param-value>
</context-param>
<error-page>
<error-code>404</error-code>
<location>/popup.jsp</location>
</error-page>
<error-page>
<exception-type>java.lang.Throwable</exception-type>
<location>/popup.jsp</location>
</error-page>
<session-config>
<session-timeout>
30
</session-timeout>
</session-config>
</web-app>