Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Connection conn = getConnection(); //run procedure getConnection to connect to the database - see below
- Statement st = conn.createStatement(); //set up a statement st to enable you to send SQL statements to the database.
- //get Java date and convert to SQL date
- java.util.Date today = new java.util.Date();
- java.sql.Date sqlToday = new java.sql.Date(today.getTime()); //date part
- //using a prepared statment to add data to a database
- try {
- PreparedStatement pstmt = null; //initalise a prepared statement pstmt
- String query = "insert into survey(id, name, Birthdate) values(?, ?, ?)"; //this is the beginning of your SQL query, the ?'s are the variables
- pstmt = conn.prepareStatement(query); // create a statement
- pstmt.setInt(1, 1); // set input parameter 1, the first ?
- pstmt.setString(2, "deptname"); // set input parameter 2, the 2nd ?
- pstmt.setDate(3, sqlToday); // set input parameter 3, the 3rd ?
- pstmt.executeUpdate(); // execute insert statement
- pstmt.close();
- }
- catch (SQLException s){
- System.out.println("SQL not executed " + s);
- }
- Statement st = conn.createStatement(); //set up a statement st to enable you to send SQL statements to the database.
- // delete the table
- st.executeUpdate("drop table survey;");
- //create the table survey
- st.executeUpdate("create table survey (id int,name varchar(30));");
- //insert records in to survey file.
- //Method 1 - create a string containing the SQL command and use st.executeUpdate(string name)to update the database
- String INSERT_RECORD = "insert into survey(id,name,Birthdate) values (1,'nameValue'," + sqlToday + ")";
- st.executeUpdate(INSERT_RECORD);
- //Method 2 - use st.executeUpdate(SQL command)
- st.executeUpdate("insert into survey (id,name) values (2,'myname')");
- st = conn.createStatement(); //reset st statement
- // querying the database
- ResultSet rs = st.executeQuery("SELECT * FROM survey"); //place the results of your query in to a special variable called rs
- while (rs.next()) //display all the results in rs - record by record
- {
- System.out.print("ID: " + rs.getInt("id"));
- System.out.print(" Name: " + rs.getString("name"));
- System.out.println(" Date: " + rs.getDate("Birthdate"));
- }
- //another query - this time with a WHERE
- ResultSet rs2 = st.executeQuery("SELECt * FROM survey WHERE (Birthdate=#12/14/2010#)");
- while (rs2.next())
- {
- System.out.print("ID: " + rs2.getInt("id"));
- System.out.print(" Name: " + rs2.getString("name"));
- System.out.println(" Date: " + rs2.getDate("Birthdate"));
- }
- st.close(); //close the database and connection
- conn.close();
- }
- private static Connection getConnection() throws Exception {
- String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
- String url = "jdbc:odbc:javaTest";
- String username = ""; //this could be input by the user
- String password = ""; //this could be input by the user
- try {
- Class.forName(driver);
- }
- catch (ClassNotFoundException cnfe) // driver not found
- {
- System.err.println ("Unable to load database driver");
- System.err.println ("Details : " + cnfe);
- System.exit(0);
- }
- return DriverManager.getConnection(url, username, password);
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement