JDBC: creating tables and inserting values to tables dynamically sample code

I believe my previously written "JDBC" post has given you a basic idea to start JDBC in a windows environment. So here I go again under the same topic but this time the post will be limited to a code. In previous post I have given codes to create a database dynamically and connecting to a database. This code shows how to create tables inside that database and inserting values dynamically into that newly created table.

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.Scanner;

public class Myjdbc {

 public static void main(String args[]) {

  System.out.println("now we are going to connect with a database ");

  try {

   Class.forName("com.mysql.jdbc.Driver");

   Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest", "root", "123");
   Statement st = con.createStatement();

   System.out.println("connection has been made ");

   Scanner scanner = new Scanner(System.in);
   System.out.println("please enter table name ");
   String tableName = scanner.next();

   String strr = null;

   System.out.println("Enter no of columns you like to use: ");
   int noc = scanner.nextInt();

   for (int i = 1; i <= noc; i++) {
    BufferedReader bf = new BufferedReader(new InputStreamReader(
      System.in));
    System.out.println("Enter column name with type ");
    String s1 = bf.readLine();
    if (strr == null) {
     strr = s1;

    } else {
     strr = strr + s1;

    }

   }

   st.executeUpdate("create table " + tableName + "(" + strr + ")");
   System.out.println("your table " + tableName
     + " has been created!!!");

   ResultSet rs = st.executeQuery("SELECT * FROM " + tableName);
   ResultSetMetaData rsmd = rs.getMetaData();
   int NumOfCol = rsmd.getColumnCount();
   System.out.println("Number of Columns of your table =" + tableName
     + " " + NumOfCol);
   System.out.println("Column names are ");
   for (int i = 1; i <= NumOfCol; i++) {
    System.out.println(rsmd.getColumnName(i));
   }
   String strn = null;
   System.out.println("please enter values you need to insert");

   for (int i = 1; i <= NumOfCol; i++) {
    String s5 = scanner.next();
    if (strn == null) {
     strn = s5;

    } else
     strn = strn + s5;

   }
   System.out.println(strn);

   st.executeUpdate("insert into " + tableName + " values" + "("
     + strn + ")");
   System.out.println("your table " + tableName
     + " has been filled!!!");

   con.close();
   System.out.println("connection has been colsed ");

  }

  catch (Exception e) {

   System.out.println(e.getMessage());
  }

 }

}

JDBC with mysql

What are the pre-requirements for starting JDBC?

• Download and install my sql community server http://www.mysql.com/downloads/mysql/
• Download Connector/J http://www.mysql.com/downloads/connector/j/

Mysql server can be started on windows using following command

NET START mysql

Mysql server can be stopped on windows using following command

NET STOP mysql

There is another way to start stop mysql on windows,right-click MyComputer manage-->Services and Applications-->Services find MySQL and toggle start stop or restart.

After downloading connector/j the mysql-connector-java-5.1.14-bin.jar file can be used as an external jar file to the JDBC project. Please refer previously written post about adding external jars for java or post for adding external jars in eclipse.

what is jdbc?

JDBC(java Database Connectivity) is Java application programming interface that allows the Java programmers to access database management system from Java code.

Creating a database with jdbc sample code

package pac1;

import java.io.*;
import java.sql.*;

public class CreateDatabase {
 public static void main(String[] args) {
  System.out.println("Database creation example!");
  Connection con = null;// Connection is an interface in java.sql package
        // that specifies connection with specific
        // database like MySql. The SQL statements are
        // executed within the context of the Connection
        // interface.
  try {
   Class.forName("com.mysql.jdbc.Driver");// this static method
             // attempts to load the
             // class and returns class
             // instance and takes string
             // type value (driver) after
             // that matches class with
             // given string
   // DriverManager is a class of java.sql package that controls a set
   // of JDBC drivers. Each driver has to be register with this class.
   con = DriverManager.getConnection("jdbc:mysql://localhost:3306",
     "root", "123");// establishes a connection to specified
         // database url
   try {
    // Statement an interface. Statement object executes the SQL
    // statement and returns the result it produces
    Statement st = con.createStatement();// method of Connection
              // interface. which
              // returns Statement
              // object. This method
              // will compile again
              // and again whenever
              // the program runs.
    BufferedReader bf = new BufferedReader(new InputStreamReader(
      System.in));
    System.out.println("Enter Database name:");
    String database = bf.readLine();
    st.executeUpdate("CREATE DATABASE " + database);// method also
                // executes SQL
                // statement
                // that may be
                // INSERT,
                // UPDATE OR
                // DELETE
                // statement are
                // used in the
                // code. It
                // takes string
                // types
                // parameters
                // for SQL
                // statement. It
                // returns int.
    System.out.println("1 row(s) affacted");
    con.close();// disconnecting from the connection, frees all the
       // resources occupied by the database.
   } catch (SQLException s) {
    System.out.print(s.getMessage());
    System.out.println("SQL statement is not executed!");
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
 }
}

Connecting with a database sample code

import java.sql.*;

public class MysqlConnect{
  public static void main(String[] args) {
    System.out.println("MySQL Connect Example.");
    
    try {
      Class.forName("com.mysql.jdbc.Driver");
      Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest","root","root");
      System.out.println("Connected to the database");
      conn.close();
      System.out.println("Disconnected from database");
   
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

Note that newly created databases get saved in C:\ProgramData\MySQL\MySQL Server 5.5\data.