Create database mysql with java

JDBC MySQL: Create Database Example

Open Eclipse IDE and create a new Java project and name it as “DBQuery“.

ConnectionFactory.java (singleton pattern)

Create a new class in src folder with the package name as “com.theopentutorials.jdbc.db” and class name as “ConnectionFactory” and click Finish.
Copy the following code:

package com.theopentutorials.jdbc.db; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class ConnectionFactory < //static reference to itself private static ConnectionFactory instance = new ConnectionFactory(); public static final String URL = "jdbc:mysql://localhost/jdbcdb"; public static final String USER = "YOUR_DATABASE_USERNAME"; public static final String PASSWORD = "YOUR_DATABASE_PASSWORD"; public static final String DRIVER_CLASS = "com.mysql.jdbc.Driver"; //private constructor private ConnectionFactory() < try < Class.forName(DRIVER_CLASS); >catch (ClassNotFoundException e) < e.printStackTrace(); >> private Connection createConnection() < Connection connection = null; try < connection = DriverManager.getConnection(URL, USER, PASSWORD); >catch (SQLException e) < System.out.println("ERROR: Unable to Connect to Database."); >return connection; > public static Connection getConnection() < return instance.createConnection(); >>

Fill the username and password for your database and enter your database name in the URL string.

DbUtil.java

Create a new class in src folder with the package name as “com.theopentutorials.jdbc.db” and class name as “DbUtil” and click Finish.
Copy the following code:

package com.theopentutorials.jdbc.db; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DbUtil < public static void close(Connection connection) < if (connection != null) < try < connection.close(); >catch (SQLException e) < /*Ignore*/ >> > public static void close(Statement statement) < if (statement != null) < try < statement.close(); >catch (SQLException e) < /*Ignore*/ >> > public static void close(ResultSet resultSet) < if (resultSet != null) < try < resultSet.close(); >catch (SQLException e) < /*Ignore*/ >> > >

DatabaseDAO.java (Data Access Object (DAO) pattern)

Create a new class in src folder with the package name as “com.theopentutorials.jdbc.dao” and class name as “DatabaseDAO” and click Finish.
Copy the following code:

package com.theopentutorials.jdbc.dao; import java.sql.Connection; import java.sql.SQLException; import java.sql.SQLWarning; import java.sql.Statement; import com.theopentutorials.jdbc.db.ConnectionFactory; import com.theopentutorials.jdbc.db.DbUtil; import com.theopentutorials.jdbc.exception.ApplicationException; public class DatabaseDAO < private Connection connection; private Statement statement; public DatabaseDAO() < >public void createDatabase(String dbName) throws ApplicationException < String query = "CREATE DATABASE IF NOT EXISTS " + dbName; try < connection = ConnectionFactory.getConnection(); statement = connection.createStatement(); statement.executeUpdate(query); SQLWarning warning = statement.getWarnings(); if (warning != null) throw new ApplicationException(warning.getMessage()); >catch (SQLException e) < ApplicationException exception = new ApplicationException( e.getMessage(), e); throw exception; >finally < DbUtil.close(statement); DbUtil.close(connection); >> >

This class defines a method createDatabase(String dbName) which creates a new database in MySQL with the given database name. If any SQL warning occurs when executing the statement such as database already exists, an application exception is created and is thrown.

DBDemo.java

This is the Java Application client class with main() method which calls the method in DAO class passing database name to be created and displays the result to the user.

Create a new Class in src folder with the package name as “com.theopentutorials.jdbc.main” and Class name as “DBDemo” and click Finish.
Copy the following code:

package com.theopentutorials.jdbc.main; import java.io.BufferedReader; import java.io.IOException; import java.io.InputStreamReader; import java.sql.SQLException; import com.theopentutorials.jdbc.dao.DatabaseDAO; import com.theopentutorials.jdbc.exception.ApplicationException; public class DBDemo < public static void main(String[] args) < // Create DB createDB(); >private static void createDB() < try < BufferedReader br = new BufferedReader(new InputStreamReader( System.in)); System.out.println("Enter the Database Name: "); String dbName = br.readLine(); DatabaseDAO dbDao = new DatabaseDAO(); dbDao.createDatabase(dbName); System.out.println("Database Successfully Created"); >catch (IOException e) < e.printStackTrace(); >catch (ApplicationException e) < System.out.println(e.getMessage()); System.out.println(e); >> >

Folder Structure:

Complete folder structure of this project is shown below.

Output

Run this DBDemo class to get the output as shown below.

Источник

Java JDBC – Create, Select and Delete MySQL Database

This chapter will explain all the database related query. It is almost your first JDBC program and it is necessary to have a database for further experiment like creating table and inserting data. In this chapter you will learn how can you create a database right from your Java Program using JDBC.

Creating Database

import java.sql.*; public class sample < static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String dburl = "jdbc:mysql://localhost/"; static final String dbuser = "root"; static final String dbpass = "root"; public static void main(String[] args) < Connection con = null; Statement stmt = null; try < //Step 1 : Connecting to Server con = DriverManager.getConnection(dburl, dbuser, dbpass); //Step 2 : Initialize Statement stmt=con.createStatement(); //Step 3 : SQL Query String query="CREATE DATABASE STOREDB"; //Step 4 : Run Query stmt.executeUpdate(query); System.out.println("Database STOREDB Created Successfully"); >catch (SQLException e) < System.err.println("Cannot connect ! "); e.printStackTrace(); >finally < System.out.println("Closing the connection."); if (con != null) try < con.close(); >catch (SQLException ignore) <> > > >

Explanation

There is 4 steps to execute any query against database.

Step 1 : Connecting to Server. You can connect to server using following line of code.

con = DriverManager.getConnection(dburl, dbuser, dbpass);

Step 2 : : Initialize Statement. Statement class is used for carrying your query to server and execute query.

Step 3 : : SQL Query. This is your sql query which is used to create database.

String query="CREATE DATABASE STOREDB";

Step 4 : : Run Query. Finally execute your query using statement object.

Select Database

It is necessary to select right database before creating table or connecting your program. There may be dozens of databases resides in server so you need to select your correct database before executing query. You have learned and created STOREDB just now. It’s time to learn how to select your STOREDB database using JDBC.

Programming Example

package SelectDatabase; import java.sql.*; public class SelectDatabase < static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String dburl = "jdbc:mysql://localhost/STOREDB"; static final String dbuser = "root"; static final String dbpass = "root"; public static void main(String[] args) < Connection con = null; Statement stmt = null; try < //Step 1 : Connecting to Server and Selecting Database con = DriverManager.getConnection(dburl, dbuser, dbpass); System.out.println("Database STOREDB Selected Successfully"); >catch (SQLException e) < System.err.println("Cannot connect ! "); e.printStackTrace(); >finally < System.out.println("Closing the connection."); if (con != null) try < con.close(); >catch (SQLException ignore) <> > > >

Explanation

Selecting Database is very easy process. Just write your database name in connecting url and that’s it.

static final String dburl = “jdbc:mysql://localhost/ STOREDB “;

Renaming Database

There is no support for renaming database in MySQL. So instead of renaming database you can create new database and import data from old database. In the next chapter you will learn how to work with Table in JDBC.

Deleting Database

Till now, you have learned how to create and select database using JDBC. Now, final task is deleting database. However, it is recommended you to after deleting database you must recreate it because this database is going to use for entire tutorial.

package SelectDatabase; import java.sql.*; public class DeleteDatabase < static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String dburl = "jdbc:mysql://localhost/"; static final String dbuser = "root"; static final String dbpass = "root"; public static void main(String[] args) < Connection con = null; Statement stmt = null; try < //Step 1 : Connecting to Server con = DriverManager.getConnection(dburl, dbuser, dbpass); //Step 2 : Initialize Statement stmt=con.createStatement(); //Step 3 : SQL Query String query="DROP DATABASE STOREDB"; //Step 4 : Run Query stmt.executeUpdate(query); System.out.println("Database STOREDB Deleted Successfully"); >catch (SQLException e) < System.err.println("Cannot connect ! "); e.printStackTrace(); >finally < System.out.println("Closing the connection."); if (con != null) try < con.close(); >catch (SQLException ignore) <> > > >

Output

Summary

In this chapter I have tried to explain with complete programming example, how can you create, select and delete database in JDBC. Once you learn how to connect and execute query against database you are able to run your own custom query against database. I hope you have enjoyed this tutorial. Next chapter is Create, Insert, Update and Delete Table in Java using JDBC.

Источник