PowerPoint Presentation
Embedded SQL
Prof: Dr. Shu-Ching Chen
TA: Sheng Guan
Problems with using interactive SQL
Standard SQL is not “Turing-complete”.
• E.g., Two profs are “colleagues” if they’ve co-taught a course.
• We can’t write a query to find all colleagues of a given professor because we have no loops or recursion.
• You can’t control the format of its output.
• And most users shouldn’t be writing SQL queries!
• You want to run queries that are based on user input, not have users writing actual queries.
http://www3.ntu.edu.sg/home/ehchua/programming/sql/relational_database_design.html
*
SQL + a conventional language
• If we can combine SQL with code in a conventional language, we can solve these problems.
• But we have another problem:
• SQL is based on relations, and conventional languages have no such type.
• It is solved by
• feeding tuples from SQL to the other language one at
a time, and
• feeding each attribute value into a particular variable.
http://www3.ntu.edu.sg/home/ehchua/programming/sql/relational_database_design.html
*
JDBC
http://www3.ntu.edu.sg/home/ehchua/programming/sql/relational_database_design.html
*
JDBC – Simple Example
Steps:
Load the driver and register it with the driver manager
Connect to a database
Create a statement
Execute a query and retrieve the results, or make changes to the database
Disconnect from the database
http://www3.ntu.edu.sg/home/ehchua/programming/sql/relational_database_design.html
*
JDBC – Simple Example
import java.sql.*;
public class jdbctest {
public static void main(String args[]){
try{
Class.forName(“org.postgresql.Driver”);
Connection con = DriverManager.getConnection
(“jdbc:postgresql://hugo.cs.fiu.edu:5432/testdb”, “user”, “pass”);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery (“select name, number
from mytable where number < 2");
while( rs.next() )
System.out.println(rs.getString(1) + " (" + rs.getInt(2) + ")");
rs.close();
stmt.close()
con.close();
} catch(Exception e){
System.err.println(e); }
} }
API for accessing and processing DB data
Java launcher looks for “main” method
“catch” an Exception here (could be an SQLException)
http://www3.ntu.edu.sg/home/ehchua/programming/sql/relational_database_design.html
*
JDBC Example.java:
Import SQL Package, Prepare DB Objects
Drafting out the sample input forms, queries and reports, often helps.
*
JDBC Example.java:
Load the driver!
Drafting out the sample input forms, queries and reports, often helps.
*
JDBC Example.java:
Make the connection to the DB..
Drafting out the sample input forms, queries and reports, often helps.
*
JDBCExample.java:
Create + Execute a Statement!
Drafting out the sample input forms, queries and reports, often helps.
*
What do I need to run this code ?
Download the driver
http://jdbc.postgresql.org/download.html
Compile the code
javac JDBCExample.java
Drafting out the sample input forms, queries and reports, often helps.
*
JDBCExample.java:
Import SQL Package, Prepare DB Objects
Drafting out the sample input forms, queries and reports, often helps.
*
JDBCExample.java: Load the driver!
Drafting out the sample input forms, queries and reports, often helps.
*
JDBCExample.java:
Make the connection to the DB..
Drafting out the sample input forms, queries and reports, often helps.
*
JDBCExample.java:
Create + Execute a Statement!
Drafting out the sample input forms, queries and reports, often helps.
*
JDBCExample.java:
INSERTs, UPDATEs..
Drafting out the sample input forms, queries and reports, often helps.
*
JDBCExample.java:
Use Prepared Statement for Insertion!
http://www3.ntu.edu.sg/home/ehchua/programming/sql/relational_database_design.html
*
JDBCExample.java:
Use Prepared Statement for Insertion!
Result of using Prepared Statement for Insertion :
http://www3.ntu.edu.sg/home/ehchua/programming/sql/relational_database_design.html
*
What is “preparation” ?
Preparing a statement includes parsing the SQL,
compiling and optimizing it.
The resulting PreparedStatement can be
executed any number of times without having to
repeat these steps.
http://www3.ntu.edu.sg/home/ehchua/programming/sql/relational_database_design.html
*
If the query isn’t known until run time
• You can hard-code in the parts you know, and use
“?” as a placeholder for the values you don’t
know.
• This is enough to allow a PreparedStatement to
be constructed.
• Once you know values for the placeholders,
methods setString, setInt, etc. let you fill in
those values.
Drafting out the sample input forms, queries and reports, often helps.
*
JDBCExample.java:
Get and Process a Result Set..
Drafting out the sample input forms, queries and reports, often helps.
*
JDBCExample.java:
DROP a table and close connection..
Drafting out the sample input forms, queries and reports, often helps.
*
Reference
http://www.cdf.toronto.edu/~csc343h/winter/slides/embedded/Embedded.pdf
https://www.coursehero.com/file/8803580/EmbeddedSQL/
Drafting out the sample input forms, queries and reports, often helps.
*