Welcome to second part of 'Java Jdbc tutorial series'.In previous discussions we came across, 'How to create a connection in JDBC ' and 'Insert data in a table in JDBC'. In this particular blog we will see 'How to create a table in jdbc' and a brief overview of 'Jdbc Statement ' and difference between them.
Project Structure
This is the overall project structure for today's discussion, we have added a 'my-sql-connector,jar' in lib folder to make the connection happen. Please make sure that the jar file is being added to the project's classpath. 'CreateConnection.java' class contains the code to create a connection with database and 'CreateTable.java' class contains the actual code to create a table in database using JDBC.
Database
This is the sample database that we have used to get a connection and create a table, execute the code below in your MySql query editor.
-- Dumping database structure for checkjdbc
CREATE DATABASE IF NOT EXISTS `checkjdbc`
/*!40100 DEFAULT CHARACTER SET latin1 */;
USE `checkjdbc`;
How to Create a table in JDBC
Create a table in jdbc is a very simple process we just need to obtain a jdbc connection and suitable statement. Then a simple sql create query is executed in that statement.\src\com\beingjavaguys\jdbc\CreateTable.java
This is a simple java class, all useful parts of code are explained in inline comments :package com.beingjavaguys.jdbc;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class CreateTable {
public void createTable() {
Statement stmt = null;
Connection con = null;
// storing a simple sql query into string object
String query = "create table user (" + "id int,"
+ "first_name varchar(30)," + "last_name varchar(30),"
+ "email varchar(30)," + "phone varchar(30))";
try {
// created connection to db
con = new CreateConnection().getConnection("checkjdbc", "root",
"root");
// obtained a statement object to execute the required query
stmt = con.createStatement();
// executed the query on obtained statement
stmt.execute(query);
System.out.println("Table created !");
} catch (SQLException e1) {
e1.printStackTrace();
} finally {
if (stmt != null) {
try {
// close statement to free the resources
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
// closed the connection to db
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
What is Statement in JDBC
Once we are done with creating a connection to DB we are able to interact with db. To execute queries we need to obtain the object from one of the three statement interfaces provided by JDBC. Statement provides useful properties and methods to create, retrieve and manipulate data into database. JDBC defined three statement interfaces listed below.1) Statement
Obtain an object of Statement interface if you need to execute static queries, it does not allows runtime parameters. Statement represents basic sql queries and is a most commonly used statement in jdbc. To obtain an statement we need to call a createStatement() object on created connection.Once a Statement is obtained we can execute sql statements, sql statements can be a simple query (i.e. select) that returns a ResultSet or some operations (i.e. Delete or Update) that manipulates data into db.
Statement provides three methods to call on it,
a) executeQuery() : This method returns a ResultSet from the provided query.
b) executeUpdate() : This method is used when we don?t want any result from db but want to perform Delete or Update like operations. This method returns an integer value equivalent to number of affected rows in a table.
c) execute() : Use this method when you don?t know whether a sql statement is going to return a result or manipulating the data. It returns a ?true? if a result is associated with it, in this case a ResultSet can be obtained by getResultSet() and number of updated rows can be obtained by getUpdateCount() method.
2) PreparedStatement
Obtain an object of PreparedStatemenr if you need to use the same statement many times in your code. This type of statements are basically used to execute precompiled queries to improve performance. PreparedStatement object accepts parameters at runtime.3) CallableStatement
Obtain an object of CallableStatement if you need to deal with sql stored procedures. JDBC programs are being executed using this type of statement. CallableStatement accepts runtime parameters.Create a connection in JDBC code
This class contains the code to create a connection to database, detailed explanation can be found from here.src\com\beingjavaguys\jdbc\CreateConnection.java
package com.beingjavaguys.jdbc;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Connection;
public class CreateConnection {
// defining driver name, mysql-jdbc driver in this case
String driverName = "com.mysql.jdbc.Driver";
Connection con = null;
// database url string with hostname and port on which db is running
String url = "jdbc:mysql://localhost:3306/";
public Connection getConnection(String dbName, String username,
String password) {
// creating connection url
String connectionUrl = url + dbName;
try {
// registers the specified driver class into memory
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
// returns a connection objcet by selecting an appropriate driver
// for specified connection URL
con = DriverManager
.getConnection(connectionUrl, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
}
Implementation class code
This is a simple java class, we have called the required code here to see the actual implementation.src\com\beingjavaguys\impl\Implementation.java
package com.beingjavaguys.impl;
import com.beingjavaguys.jdbc.CreateTable;
public class Implementation {
public static void main(String args[]) {
// create a table
CreateTable createTable = new CreateTable();
createTable.createTable();
}
}
In this particular blog we came across how to create a table in jdbc and a brief about statement in jdbc. In upcoming blogs we will see more about JDBC including JDBC Create Connection & JDBC Inset Query & JDBC Delete and JDBC Update Implementation in Java.