· 9 years ago · Mar 26, 2017, 01:18 PM
1/**
2* Class: B.Sc. in Computing
3* Description: A JDBCFacebookDB class - Contains methods to work with the Facebook database
4* Date: 02/03/2017
5* @author Maria Boyle
6* @version 1.0
7*/
8import java.sql.Connection;
9import java.sql.DriverManager;
10import java.sql.SQLException;
11import java.sql.Statement;
12import java.sql.ResultSet;
13
14public class JDBCFacebookDB implements JDBCDB{
15 // JDBC Driver name, database URL, user name and password
16 private final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
17 private final String USER_NAME = "root";
18 private final String PASSWORD = "password";
19
20 private Connection conn;
21 private Statement stmt;
22
23 // Constructor
24 public JDBCFacebookDB(){
25 }
26
27 // createConnection() - Registers JDBC Driver, and opens a connection to dbUrl
28 public void createConnection(String dbUrl){
29 try{
30 // STEP 1 - Load the JDBC driver
31 // Initialize a driver to open a communications channel with the database.
32 Class.forName(JDBC_DRIVER);
33 System.out.println("STEP 1 COMPLETE - Driver Registered...");
34
35 // STEP 2 - Open a connection
36 // Use the DriverManager.getConnection() method to create a Connection object,
37 // which represents a physical connection with the database server.
38 conn = DriverManager.getConnection(dbUrl, USER_NAME, PASSWORD);
39 System.out.println("STEP 2 COMPLETE - Connection obtained...");
40 }
41 catch (ClassNotFoundException e){
42 System.out.println("Could not load driver.\n" + e.getMessage());
43 }
44 catch (SQLException e) {
45 System.out.println("Problem with SQL.\n" + e.getMessage());
46 }
47 }
48
49 // createDatabase() - Calls createConnection(), creates a database called facebook with a table called user
50 public void createDatabase(){
51 try{
52 // createConnection() to localhost
53 createConnection("jdbc:mysql://localhost/");
54
55 // Create Statement object
56 stmt = conn.createStatement();
57 System.out.println("COMPLETE - Statement object created...");
58
59 // Execute Update to Create a Database called facebook
60 String createDatabase = "CREATE DATABASE IF NOT EXISTS facebook";
61 stmt.executeUpdate(createDatabase);
62 System.out.println("COMPLETE - Update executed and facebook database created...");
63
64 // Execute Update to Create a Table called user
65 String createTable = "CREATE TABLE IF NOT EXISTS user " +
66 "(emailaddress VARCHAR(24) not NULL, " +
67 " password VARCHAR(18), " +
68 " firstname VARCHAR(20), " +
69 " lastname VARCHAR(20), " +
70 " PRIMARY KEY (emailaddress))";
71
72 stmt.executeUpdate("USE facebook");
73 stmt.executeUpdate(createTable);
74 System.out.println("COMPLETE - Update executed and user table added to facebook database...");
75
76 // closeConnection()
77 closeConnection();
78 }
79 catch (SQLException e){
80 System.out.println("Problem with SQL.\n" + e.getMessage());
81 }
82 }
83
84 // closeConnection() - Closes the connection
85 public void closeConnection(){
86 try{
87 if(conn != null){
88 conn.close();
89 System.out.println("COMPLETE - Connection closed.");
90 }
91 }
92 catch (SQLException e){
93 System.out.println("Could not close connection.\n" + e.getMessage());
94 }
95 }
96
97
98 public void insertIntoDatabase(String sqlString) {
99 try{
100 // createConnection() to localhost
101 createConnection("jdbc:mysql://localhost/");
102
103 // Create Statement object
104 stmt = conn.createStatement();
105 System.out.println("COMPLETE - Statement object created...");
106
107 // Execute Update to Input some data into table
108 String insertIntoDatabase = sqlString;
109 stmt.executeUpdate("USE facebook");
110 stmt.executeUpdate(insertIntoDatabase);
111 System.out.println("COMPLETE - Update executed, data has been entered");
112
113 // closeConnection()
114 closeConnection();
115 }
116 catch (SQLException e){
117 System.out.println("Problem with SQL.\n" + e.getMessage());
118 }
119 }
120
121
122
123
124
125
126}