Monday 6 March 2017

Chapter 32 Exercise 9, Introduction to Java Programming, Tenth Edition Y. Daniel LiangY.

32.9 Copy table) Suppose the database contains a student table defined as follows:
create table Student1 (
username varchar(50) not null,
password varchar(50) not null,
fullname varchar(200) not null,
constraint pkStudent primary key (username)
);
Create a new table named Student2 as follows:
create table Student2 (
username varchar(50) not null,
password varchar(50) not null,
firstname varchar(100),
lastname varchar(100),
constraint pkStudent primary key (username)
);
A full name is in the form of firstname mi lastname or firstname
lastname . For example, John K Smith is a full name. Write a program that copies table Student1 into Student2 . Your task is to split a full name into
firstname , mi , and lastname for each record in Student1 and store a new
record into Student2 .


import java.io.IOException;
import java.net.MalformedURLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class Exercise09 {

 public static void main(String[] args) throws MalformedURLException, IOException, ClassNotFoundException, SQLException {
  
  Class.forName("com.mysql.jdbc.Driver");
  Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/javabook", "root", "root");
  Statement statement = connection.createStatement();
  ResultSet rset = statement.executeQuery("select * from Student1");
  
  String queryString = "insert into Student2 (username, password, firstname, lastname) values (?, ?, ?, ?);";
  while(rset.next()) {
   PreparedStatement preparedStatement = connection.prepareStatement(queryString);
   preparedStatement.setString(1, rset.getString(1));
   preparedStatement.setString(2, rset.getString(2));
   
   Scanner name = new Scanner(rset.getString(3));
   String name1 = name.next();
   String name2 = name.next();
   if(name.hasNext()) {
    preparedStatement.setString(3, name1 + " " + name2);
    preparedStatement.setString(4, name.next());    
   } else {
    preparedStatement.setString(3, name1);
    preparedStatement.setString(4, name2);    
   }
   name.close();
   
   preparedStatement.executeUpdate();
  }
  
 }

}

No comments :

Post a Comment