程序代写代做代考 database Java JDBC SQL PowerPoint Presentation

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