insert java code to SQL

[rashidzamani]

Hello,
i am working on a database to store date on it.
this is my code:

int day =7;
                int month=11;
                while(day<32){
                    if(day>30){
                        day=day -30;
                        month =12;
                    }
        execute2("INSERT INTO Customer(CID,TID,People) VALUES(0,2,0)");
        s="INSERT INTO Tour VALUES(0,1,'2009-month-day')";
        if(execute(s))
            System.out.println("\t Value Inserted");
        else
            System.out.println("\t Value NOT Inserted");
        day = day+7;
                }

i want to insert each saturday of november and december to my data base.

i just wonder if what i did is ok or it adds month and day as string to data base.

[RamyaSivakanth]

Hi,
Nobody can give solution with assumptions.

1.What is the data type you are using for dateformat in the table?

2.Send the complete code.

3.If you are inserting more than 1 column value and bunch of records ,use PreparedStatement.

-Regards
Ramya

[rashidzamani]

it is string! and this a class of the software which connevts it to the database

package mynature;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Rashid
 */
public class DataBase {
    
    private Connection conn = null;
    private String userName = "root";
    private String password = "root";
    private String url = "jdbc:mysql:///mynature";

    public ArrayList<Customer> customerData = new ArrayList<Customer>();

    public DataBase()
    {
        if(connect())
        {
            ResultSet rs=executeQuery("show tables;");
            try {
                if (!rs.first()) //database is empty
                    CreateTables();
            } catch (SQLException ex) {
                Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
            }
        } 
        else
            System.out.println("Error connecting to database");
    }
    
    
    public ArrayList<String> FindProperTour(String TourName, String quantity){
        ArrayList<String> l=new ArrayList<String>();
        try {
            int q= Integer.parseInt(quantity);
            ResultSet rs = executeQuery("call alltours()");
            rs.first();
            while(!rs.isAfterLast())
            {
                if(rs.getString("TName").equalsIgnoreCase(TourName) && rs.getInt("Capacity")-rs.getInt("SumOfPeople")>=q)
                    l.add(rs.getString("TDate"));
                rs.next();
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        return l;
    }
    
    public Customer RefSearch(int refNo){
        return null;
    }
    
    public Customer NameSearch(String CustomerName){
        return null;
    }
    
    public Tour GetTourInfo(String TourName) {
        return null;
    }
    
    public String GetRefNo(){
        return "A23GH";
    }
    
    public void SaveCustomer(Customer customer){
        String TDate=Main.frmBooking.jComboBox2.getSelectedItem().toString();
        String TourType=Main.frmBooking.jComboBox1.getSelectedItem().toString();
        int cnt=Integer.parseInt(Main.frmBooking.jTextField1.getText());
        String s="SELECT t.TID as tourid FROM tourtype as tt, tour as t where tt.TTID=t.TTID AND tt.TName='"+TourType+"' AND t.TDate='"+TDate+"'";
        ResultSet rs= executeQuery(s);
        System.out.println(s);
        String TourID=" ";
        try {
            TourID = rs.getString("tourid");
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        s="INSERT INTO Customer VALUES(0,"+TourID+","+cnt+","+customer.GetTel()+ 
                "," +customer.GetPostCode()+
                "," +customer.GetAdd()+
                "," +customer.GetCreditCardNo()+
                "," +customer.GetRefNo()+
                "," +customer.GetCity()+
                "," +customer.GetCountry()+
                ")";
        System.out.println(s);
        execute2(s);
    }
    
    public Customer FindCustomerByName(String name){
        return null;
    }
    
    public Customer FindCustomerByRefNo(String RefNo){
        return null;
    }
    
    /*public void DeleteCustomer( nemidoonam chi bezanam, hame name mishe dad, ham refrence no.){
        
    }
*/

    public boolean connect(){
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            this.conn = (Connection) DriverManager.getConnection(
                    this.url, this.userName, this.password
            );
            System.out.println ("Database connection established");
        } catch (Exception e) {
            System.out.println ("Database connection ERROR : "+e.toString());
            return false;
        }
        return true;
    }

    public Statement creatStatement(){

        Statement s = null;
        try {
            s = (Statement)this.conn.createStatement();
        } catch (SQLException se) {
            System.out.println("We got an exception while creating a statement:"
                             + "that probably means we're not connected.");
            System.out.println(se.getMessage());
            System.exit(1);
        }
        return s;
    }

    public void execute2(String query){
        boolean b=execute(query);
    }
    
    public boolean  execute(String query){

        try {
            this.creatStatement().execute(query);
            return true;
        } catch (SQLException se) {
            System.out.println("We got an exception while executing our query:"
                             + "that probably means our SQL is invalid : \n\t"+se.getMessage());
            se.printStackTrace();
            System.exit(1);
        }
        return false;
    }
    
    public ResultSet executeQuery(String query){

        ResultSet rs = null;
        try {
            rs = this.creatStatement().executeQuery(query);
        } catch (SQLException se) {
            System.out.println("We got an exception while executing our query:"
                             + "that probably means our SQL is invalid : \n\t"+se.getMessage());
            se.printStackTrace();
            System.exit(1);
        }
        return rs;
    }

    public void CreateTables()
    {
        String s;
        s="CREATE TABLE TourType(" +
                                        "TTID INT AUTO_INCREMENT," +
                                        "TName VARCHAR(50)," +
                                        "TDays INT," +
                                        "Destination VARCHAR(50)," +
                                        "Capacity INT," +
                                        "Cost INT," +
                                        "PRIMARY KEY (TTID))";
        if(execute(s))
            System.out.println("TourType created");
        else
            System.out.println("TourType NOT created");
        
        s="INSERT INTO TourType VALUES(0,'Sarek',8,'Sarek',12,35000)";
        if(execute(s))
            System.out.println("\t Value Inserted");
        else
            System.out.println("\t Value NOT Inserted");

        s="INSERT INTO TourType VALUES(0,'Åre',4,'Åre',20,15000)";
        if(execute(s))
            System.out.println("\t Value Inserted");
        else
            System.out.println("\t Value NOT Inserted");

        s="INSERT INTO TourType VALUES(0,'Jukkasjärvi',6,'Jukkasjärvi',6,45000)";
        if(execute(s))
            System.out.println("\t Value Inserted");
        else
            System.out.println("\t Value NOT Inserted");
        
        s="INSERT INTO TourType VALUES(0,'Kiruna',4,'Kiruna',30,45000)";
        if(execute(s))
            System.out.println("\t Value Inserted");
        else
            System.out.println("\t Value NOT Inserted");

        s="CREATE TABLE Customer(" +
                                        "CID INT AUTO_INCREMENT," +
                                        "TID INT," +
                                        "People INT," +
                                        "Tel VARCHAR(50)," +
                                        "PostCode VARCHAR(50)," +
                                        "Address VARCHAR(50)," +
                                        "CreditCardNo VARCHAR(50)," +
                                        "RefNo VARCHAR(50)," +
                                        "City VARCHAR(50)," +
                                        "Country VARCHAR(50)," +
                                        "PRIMARY KEY (CID))";
        if(execute(s))
            System.out.println("Customer created");
        else
            System.out.println("Customer NOT created");
        s="CREATE TABLE Tour(" +
                                        "TID INT AUTO_INCREMENT," +
                                        "TTID INT," +
                                        "TDate VARCHAR(50)," +
                                        "PRIMARY KEY (TID))";
        if(execute(s))
            System.out.println("Tour created");
        else
            System.out.println("Tour NOT created");

        execute2("INSERT INTO Customer(CID,TID,People) VALUES(0,1,0)");
        s="INSERT INTO Tour VALUES(0,1,'2009-10-05')";
            if(execute(s))
                System.out.println("\t Value Inserted");
            else
                System.out.println("\t Value NOT Inserted");
                int day =7;
                int month=11;
                while(day<32){
                    if(day>30){
                        day=day -30;
                        month =12;
                    }
        execute2("INSERT INTO Customer(CID,TID,People) VALUES(0,2,0)");
        s="INSERT INTO Tour VALUES(0,1,'2009-month-day')";
        if(execute(s))
            System.out.println("\t Value Inserted");
        else
            System.out.println("\t Value NOT Inserted");
        day = day+7;
                }
        execute2("INSERT INTO Customer(CID,TID,People) VALUES(0,3,0)");
        s="INSERT INTO Tour VALUES(0,1,'2009-10-20')";
        if(execute(s))
            System.out.println("\t Value Inserted");
        else
            System.out.println("\t Value NOT Inserted");
        
        execute2("INSERT INTO Customer(CID,TID,People) VALUES(0,4,0)");
        s="INSERT INTO Tour VALUES(0,1,'2009-10-25')";
        if(execute(s))
            System.out.println("\t Value Inserted");
        else
            System.out.println("\t Value NOT Inserted");

        execute2("INSERT INTO Customer(CID,TID,People) VALUES(0,5,0)");
        s="INSERT INTO Tour VALUES(0,2,'2009-10-01')";
        if(execute(s))
            System.out.println("\t Value Inserted");
        else
            System.out.println("\t Value NOT Inserted");
        execute2("INSERT INTO Customer(CID,TID,People) VALUES(0,6,0)");
        s="INSERT INTO Tour VALUES(0,2,'2009-11-01')";
        if(execute(s))
            System.out.println("\t Value Inserted");
        else
            System.out.println("\t Value NOT Inserted");
        execute2("INSERT INTO Customer(CID,TID,People) VALUES(0,7,0)");
        s="INSERT INTO Tour VALUES(0,2,'2009-12-01')";
        if(execute(s))
            System.out.println("\t Value Inserted");
        else
            System.out.println("\t Value NOT Inserted");

        execute2("INSERT INTO Customer(CID,TID,People) VALUES(0,8,0)");
        s="INSERT INTO Tour VALUES(0,3,'2009-10-05')";
        if(execute(s))
            System.out.println("\t Value Inserted");
        else
            System.out.println("\t Value NOT Inserted");
        execute2("INSERT INTO Customer(CID,TID,People) VALUES(0,9,0)");
        s="INSERT INTO Tour VALUES(0,3,'2009-11-05')";
        if(execute(s))
            System.out.println("\t Value Inserted");
        else
            System.out.println("\t Value NOT Inserted");
        execute2("INSERT INTO Customer(CID,TID,People) VALUES(0,10,0)");
        s="INSERT INTO Tour VALUES(0,3,'2009-12-05')";
        if(execute(s))
            System.out.println("\t Value Inserted");
        else
            System.out.println("\t Value NOT Inserted");
        execute2("INSERT INTO Customer(CID,TID,People) VALUES(0,11,0)");
        s="INSERT INTO Tour VALUES(0,3,'2009-12-30')";
        if(execute(s))
            System.out.println("\t Value Inserted");
        else
            System.out.println("\t Value NOT Inserted");


        s="CREATE PROCEDURE alltours() BEGIN" +
                " SELECT TourType.TName, tour.TDate, TourType.Capacity," +
                " Sum(customer.people) AS SumOfpeople"+
                " FROM (customer INNER JOIN tour ON customer.TID = tour.TID)" +
                " INNER JOIN TourType ON tour.TTID = TourType.TTID " +
                " GROUP BY TourType.TName, tour.TDate, TourType.Capacity; " +
                " END";
        if(execute(s))
            System.out.println("Proc alltours created");
        else
            System.out.println("Proc alltours NOT created");
    }

    public ArrayList<String> getAllTourTypes()
    {
        ArrayList<String> l=new ArrayList<String>();
        try {
            ResultSet rs = executeQuery("Select * from tourtype");
            rs.first();
            while(!rs.isAfterLast())
            {
                l.add(rs.getString("TName"));
                rs.next();
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        return l;
    }
    
    public boolean disconnect() {

        if (conn != null) {
            try {
                conn.close ();
                System.out.println ("Database connection terminated");
            } catch (Exception e) {
                return false;
            }
        }
        return true;
    }
}

[RamyaSivakanth]

Hi,
type is varchar folr the date.So,what you are doing seems to be correct.
But ,try to optimize code and using object oriented flow.
Use PreparedStatement.Don't again and again insert these many queries .
Go thru suntutorial.
Use code tags when u are posting the code.
-Regards
Ramya