Advertisement
Guest User

Untitled

a guest
Jul 22nd, 2017
90
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 3.48 KB | None | 0 0
  1. Connection conn = getConnection(); //run procedure getConnection to connect to the database - see below
  2.  
  3.  
  4.     Statement st = conn.createStatement(); //set up a statement st to enable you to send SQL statements to the database.
  5.    
  6.     //get Java date and convert to SQL date
  7.     java.util.Date today = new java.util.Date();
  8.     java.sql.Date sqlToday = new java.sql.Date(today.getTime()); //date part
  9.  
  10.  
  11.     //using a prepared statment to add data to a database
  12.     try {
  13.         PreparedStatement pstmt = null; //initalise a prepared statement pstmt
  14.  
  15.  
  16.         String query = "insert into survey(id, name, Birthdate) values(?, ?, ?)"; //this is the beginning of your SQL query, the ?'s are the variables
  17.        
  18.         pstmt = conn.prepareStatement(query); // create a statement
  19.  
  20.  
  21.         pstmt.setInt(1, 1);              // set input parameter 1, the first ?
  22.         pstmt.setString(2, "deptname");  // set input parameter 2, the 2nd ?
  23.         pstmt.setDate(3, sqlToday);      // set input parameter 3, the 3rd ?
  24.  
  25.  
  26.         pstmt.executeUpdate(); // execute insert statement
  27.         pstmt.close();
  28.     }
  29.     catch (SQLException s){
  30.         System.out.println("SQL not executed " + s);
  31.        
  32.     }
  33.  
  34.  
  35.       Statement st = conn.createStatement(); //set up a statement st to enable you to send SQL statements to the database.
  36.  
  37.     // delete the table
  38.     st.executeUpdate("drop table survey;");
  39.  
  40.     //create the table survey
  41.     st.executeUpdate("create table survey (id int,name varchar(30));");
  42.  
  43.     //insert records in to survey file.
  44.  
  45.     //Method 1 - create a string containing the SQL command and use st.executeUpdate(string name)to update the database    
  46.     String INSERT_RECORD = "insert into survey(id,name,Birthdate) values (1,'nameValue'," + sqlToday + ")";
  47.     st.executeUpdate(INSERT_RECORD);
  48.  
  49.     //Method 2 - use st.executeUpdate(SQL command)
  50.     st.executeUpdate("insert into survey (id,name) values (2,'myname')");
  51.  
  52.  
  53.  
  54.     st = conn.createStatement(); //reset st statement
  55.  
  56.     // querying the database
  57.     ResultSet rs = st.executeQuery("SELECT * FROM survey"); //place the results of your query in to a special variable called rs
  58.  
  59.     while (rs.next()) //display all the results in rs - record by record
  60.     {
  61.         System.out.print("ID: " + rs.getInt("id"));
  62.         System.out.print(" Name: " + rs.getString("name"));
  63.         System.out.println(" Date: " + rs.getDate("Birthdate"));
  64.  
  65.     }
  66.  
  67.    //another query - this time with a WHERE
  68.     ResultSet rs2 = st.executeQuery("SELECt * FROM survey WHERE (Birthdate=#12/14/2010#)");
  69.     while (rs2.next())
  70.     {
  71.         System.out.print("ID: " + rs2.getInt("id"));
  72.         System.out.print(" Name: " + rs2.getString("name"));
  73.         System.out.println(" Date: " + rs2.getDate("Birthdate"));
  74.      
  75.     }
  76.  
  77.  
  78.  
  79.     st.close(); //close the database and connection
  80.     conn.close();
  81.   }
  82.  
  83.  
  84.  
  85.   private static Connection getConnection() throws Exception {
  86.     String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
  87.     String url = "jdbc:odbc:javaTest";
  88.     String username = ""; //this could be input by the user
  89.     String password = ""; //this could be input by the user
  90.     try {
  91.         Class.forName(driver);
  92.     }
  93.     catch (ClassNotFoundException cnfe) // driver not found
  94.     {
  95.         System.err.println ("Unable to load database driver");
  96.         System.err.println ("Details : " + cnfe);
  97.         System.exit(0);
  98.     }
  99.  
  100.       return DriverManager.getConnection(url, username, password);  
  101.   }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement