0

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>
Giri
  • 1
  • how can you connect to a db before it's created – Madhawa Priyashantha Dec 17 '17 at 06:23
  • I know my database does not exists and that is what I want initially. My code will create a database with the name specified if it does not exist. But the thing is that it is not able to create one. – Giri Dec 17 '17 at 07:07
  • try to remove 'database' name from the second connection attempt. then create the database, and then connect again with database name. – YyYo Dec 17 '17 at 08:26

0 Answers0