Results 1 to 4 of 4
  1. #1
    rashidzamani is offline Member
    Join Date
    Aug 2009
    Posts
    2
    Rep Power
    0

    Default insert java code to SQL

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

    Java 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.

  2. #2
    RamyaSivakanth's Avatar
    RamyaSivakanth is offline Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    843
    Rep Power
    6

    Default

    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
    Ramya:cool:

  3. #3
    rashidzamani is offline Member
    Join Date
    Aug 2009
    Posts
    2
    Rep Power
    0

    Default

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

    Java Code:
    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;
        }
    }

  4. #4
    RamyaSivakanth's Avatar
    RamyaSivakanth is offline Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    843
    Rep Power
    6

    Default

    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
    Ramya:cool:

Similar Threads

  1. Replies: 3
    Last Post: 02-28-2009, 10:17 AM
  2. java binary search and insert
    By pansylea in forum New To Java
    Replies: 6
    Last Post: 02-23-2009, 05:54 PM
  3. Replies: 5
    Last Post: 10-20-2008, 07:31 AM
  4. How to insert java Object in oracle database
    By Thilkumar82 in forum Advanced Java
    Replies: 9
    Last Post: 08-13-2008, 12:33 PM
  5. How to insert graph in java
    By valery in forum Advanced Java
    Replies: 1
    Last Post: 08-06-2007, 09:38 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •