Ruben Gamboa
Professor
CREATE SCHEMA . . . command
CREATE SCHEMA MovieInformationSET SCHEMA MovieInformationUSE MovieInformationThis may be as simple as
CONNECT TO server_name AS connection_name AUTHORIZATION username AND password
Later, you can (and should) release the connection with
DISCONNECT connection_name
EXEC SQL BEGIN DECLARE SECTION;
char studioName[50];
int presNetWorth;
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
strcpy(studioName, "Lucasfilm");
EXEC SQL SELECT netWorth INTO :presNetWorth
FROM Studios JOIN MovieExecs ON presC# = cert#
WHERE Studios.name = :studioName;
printf ("The president of Lucasfilm is worth $%d", presNetWorth);
EXEC SQLDECLARE SECTION blocksEXEC SQL BEGIN DECLARE SECTION;
char studioName[50];
int presNetWorth;
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
EXEC SQL BEGIN DECLARE SECTION;
. . .
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
If you declare a variable with this name (and you should), it will receive a special status code from the last SQL statement
Many of the codes are standard (and many more are database-specific)
Close the cursor when finished
EXEC SQL DECLARE cursor_name CURSOR FOR query;
EXEC SQL OPEN cursor_name;
EXEC SQL FETCH FROM cursor_name INTO :var1, :var2, . . ., :varn;
. . .
EXEC SQL CLOSE cursor_name;
EXEC SQL BEGIN DECLARE SECTION;
int cert_number;
char title[100];
int year;
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE c_movies CURSOR FOR
SELECT title, year
FROM Movies
WHERE presC# = :cert_number
ORDER BY year;
EXEC SQL OPEN c_movies;
for (;;) {
EXEC SQL FETCH FROM c_movies INTO :title, :year;
if (!strcmp(SQLSTATE,"02000"))
break;
printf ("%d, %s\n", year, title);
}
EXEC SQL CLOSE c_movies;
EXEC SQL DECLARE c_emps CURSOR FOR
SELECT employee_id, rating, salary
FROM Employees;
EXEC SQL OPEN c_emps;
for (;;) {
EXEC SQL FETCH FROM c_emps INTO :id, :rating, :salary;
if (!strcmp(SQLSTATE,"02000"))
break;
if (rating >= 9)
EXEC SQL UPDATE Employees
SET salary = 1.1 * :salary
WHERE CURRENT OF c_emps;
else if (rating <= 2)
EXEC SQL DELETE FROM Employees
WHERE CURRENT OF c_emps;
}
EXEC SQL CLOSE c_emps;
E.g., your application can build a string by appending conditions to some base
q = "SELECT * FROM Employees WHERE rating >= 10";
q = q + " AND location='US'";
q = q + " AND last_name LIKE '%jones%";
Note: You should avoid dynamic SQL
Repeat: Avoid dynamic SQL
EXEC SQL BEGIN DECLARE SECTION;
int cert_number;
char *query = "SELECT title, year
FROM Movies
WHERE presC# = :cert_number";
EXEC SQL END DECLARE SECTION;
EXEC SQL PREPARE sql_query FROM :query;
EXEC SQL DECLARE c_query CURSOR FOR sql_query;
cert_number = 12345;
EXEC SQL OPEN c_query;
for (;;) {
EXEC SQL FETCH FROM c_query INTO :title, :year;
if (!strcmp(SQLSTATE,"02000"))
break;
printf ("%d, %s\n", year, title);
}
EXEC SQL CLOSE c_movies;
EXEC SQL BEGIN DECLARE SECTION;
int cert_number;
char *query = "SELECT title, year
FROM Movies
WHERE presC# = ?";
EXEC SQL END DECLARE SECTION;
EXEC SQL PREPARE sql_query FROM :query;
EXEC SQL DECLARE c_query CURSOR FOR sql_query;
cert_number = 12345;
EXEC SQL OPEN c_query USING :cert_number;
for (;;) {
EXEC SQL FETCH FROM c_query INTO :title, :year;
if (!strcmp(SQLSTATE,"02000"))
break;
printf ("%d, %s\n", year, title);
}
EXEC SQL CLOSE c_movies;
CREATE PROCEDURE Move(IN oldAddr VARCHAR(255),
IN newAddr VARCHAR(255))
BEGIN
UPDATE MovieStar
SET address = newAddr
WHERE address = oldAddr;
END;
CALL Move ('1 Innovation Way', '1 Infinite Loop');
EXEC SQL CALL Move ('1 Innovation Way', '1 Infinite Loop');
Note: SQL recognizes IN, OUT, and INOUT parameters
CREATE FUNCTION NumMovies(IN name VARCHAR(255)) RETURNS NUMBER
BEGIN
RETURN (SELECT COUNT(*)
FROM StarsIn
WHERE StarsIn.starName = name);
END;
SELECT name, NumMovies(name)
FROM Stars
WHERE NumMovies(name) > 10
CREATE FUNCTION NumMovies(IN name VARCHAR(255)) RETURNS NUMBER
DECLARE movieCount NUMBER;
BEGIN
SET movieCount = (SELECT COUNT(*)
FROM StarsIn
WHERE StarsIn.starName = name);
RETURN movieCount;
END;
IF . . . THEN . . . ELSEIF . . . THEN . . . ELSE . . . END IFCREATE FUNCTION StarList(IN name VARCHAR(255)) RETURNS NUMBER
DECLARE movieCount NUMBER;
BEGIN
SET movieCount = NumMovies(name);
IF movieCount > 10 THEN
RETURN 'A-list';
ELSEIF movieCount > 5 THEN
RETURN 'B-list';
ELSE
RETURN 'C-list';
ENDIF;
END;
Note: EXISTS and NOT EXISTS are used in the condition often
CREATE FUNCTION triangle(IN n NUMBER) RETURNS NUMBER
DECLARE i NUMBER;
DECLARE t NUMBER;
BEGIN
SET t = 0;
SET i = 0;
mainLoop: LOOP
IF i > n THEN
LEAVE mainLoop;
END IF;
SET t = t + i;
SET i = i + 1;
END LOOP;
RETURN t;
END;
CREATE FUNCTION AvgNumMovies() RETURNS NUMBER
DECLARE movieSum NUMBER;
DECLARE starCount NUMBER;
DECLARE starName VARCHAR(255);
DECLARE movieCount NUMBER;
DECLARE movieCursor FOR
SELECT movieStar, COUNT(movieTitle)
FROM StarsIn
GROUP BY movieStar;
BEGIN
SET movieSum = 0;
SET starCount = 0;
OPEN movieCursor;
mainLoop: LOOP
FETCH FROM movieCursor INTO starName, movieCount;
IF SQLSTATE '02000' THEN
LEAVE mainLoop;
END IF;
SET movieSum = movieSum + movieCount;
SET starCount = starCount + 1;
END LOOP;
CLOSE movieCursor;
RETURN movieSum / starCount;
END;
CREATE FUNCTION AvgMovieLength(IN s VARCHAR(255)) RETURNS NUMBER
DECLARE movieSum NUMBER;
DECLARE movieCount NUMBER;
DECLARE title VARCHAR(255);
DECLARE year NUMBER;
DECLARE length NUMBER;
DECLARE movieCursor FOR
SELECT title, year, length
FROM Movies
WHERE studioName = s;
DECLARE NoMoreRows CONDITION FOR SQLSTATE '02000';
BEGIN
SET movieSum = 0;
SET movieCount = 0;
OPEN movieCursor;
mainLoop: LOOP
FETCH FROM movieCursor INTO title, year, length;
IF NoMoreRows THEN
LEAVE mainLoop;
END IF;
SET movieSum = movieSum + length;
SET movieCount = movieCount + 1;
END LOOP;
CLOSE movieCursor;
RETURN movieSum / movieCount;
END;
CREATE FUNCTION AvgMovieLength(IN s VARCHAR(255)) RETURNS NUMBER
DECLARE movieSum NUMBER;
DECLARE movieCount NUMBER;
BEGIN
SET movieSum = 0;
SET movieCount = 0;
FOR movieLoop AS movieCursor CURSOR FOR
SELECT title, year, length
FROM Movies
WHERE studioName = s;
DO
SET movieSum = movieSum + length;
SET movieCount = movieCount + 1;
END LOOP;
RETURN movieSum / movieCount;
END;
CREATE FUNCTION triangle(IN n NUMBER) RETURNS NUMBER
DECLARE i NUMBER;
DECLARE t NUMBER;
BEGIN
SET t = 0;
SET i = 0;
WHILE i <= n DO
SET t = t + i;
SET i = i + 1;
END WHILE;
RETURN t;
END;
CREATE FUNCTION triangle(IN n NUMBER) RETURNS NUMBER
DECLARE i NUMBER;
DECLARE t NUMBER;
BEGIN
SET t = 0;
SET i = 0;
REPEAT
SET t = t + i;
SET i = i + 1;
UNTIL i > n
END REPEAT;
RETURN t;
END;
CREATE FUNCTION GetYear(IN t VARCHAR(255)) RETURNS NUMBER
DECLARE NoMoreRows CONDITION FOR SQLSTATE '02000';
DECLARE TooManyRows CONDITION FOR SQLSTATE '21000';
BEGIN
DECLARE EXIT HANDLER FOR NoMoreRows, TooManyRows
RETURN NULL;
RETURN (SELECT year FROM Movies WHERE title = t);
END;
DECLARE EXIT HANDLER FOR NoMoreRows, TooManyRows
SET year = NULL;
Advantages:
Disadvantage:
Class.forName("jdbc.driver.Oracledriver");
Class.forName("jdbc.driver.Oracledriver");
String url = "jdbc:oracle:www.bookstore.com:3083";
Connection conn;
try {
con = DriverManager.getConnection(url, "scott", "tiger");
}
catch (SQLException e) {
// handle error
}
The basic idea is not to write code like this:
Class.forName("jdbc.driver.Oracledriver");
String url = "jdbc:oracle:www.bookstore.com:3083";
Connection conn;
try {
con = DriverManager.getConnection(url, "scott", "tiger");
}
Notice all the assumptions this little bit of code is making about its environment
Instead, write code like this
class Foo {
public void setDriverName (String driver) { this.dbDriver = driver; }
public void setConnectionUrl (String url) { this.dbUrl = url; }
public void setDbUser (String user) { this.dbUser = user; }
public void setDbPassword (String password) { this.dbPassword = password; }
. . .
public void connect() {
Class.forName(dbDriver);
Connection con;
try {
DriverManager.getConnection (dbUrl, dbUser, dbPassword);
}
. . .
}
}
JDBC offers three classes for interacting with SQL statements
You can call executeUpdate() or executeQuery() to execute a query
// The "?" indicate where the parameters go
String sql="INSERT INTO Movies (title, year, genre, length) VALUES(?,?,?,?)";
PreparedStatment pstmt=con.prepareStatement(sql);
pstmt.clearParameters(); // not needed here
pstmt.setString(1, title); // one- (not zero-)based offsets
pstmt.setInt(2, year);
pstmt.setString(3, genre);
pstmt.setInt(4, length);
// no rows are returned, thus we use executeUpdate()
int numRowsChanged = pstmt.executeUpdate();
Note: You can use column names instead of position indexes in the getXXX() and setXXX() methods
ResultSet rs=pstmt.executeQuery(sql);
// rs is now a cursor
while (rs.next()) {
// process the data
}
rs.close ();
| SQL Type | Java Class | ResultSet get/set Method |
|---|---|---|
| BIT | Boolean | getBoolean() |
| CHAR | String | getString() |
| VARCHAR | String | getString() |
| DOUBLE | Double | getDouble() |
| FLOAT | Double | getDouble() |
| INTEGER | Integer | getInt() |
| REAL | Double | getFloat() |
| DATE | java.sql.Date | getDate() |
| TIME | java.sql.Time | getTime() |
| TIMESTAMP | java.sql.TimeStamp | getTimestamp() |
If a field is NULL, the getXXX() will return Java null if possible
String title = rs.getString(1);
if (title == null) {
. . .
}
But some types (e.g., int, float, double) do not have NULL equivalents
In this case, you can use wasNull() to check
int year = rs.getInt(2);
if (rs.wasNull()) {
. . .
}
String sql = "SELECT id FROM login " +
"WHERE username = '" + form.username + "'" +
"AND password = '" + form.password + "'";
// Now execute the SQL statement to get the id for the user
// (if the username and password are correct)
SELECT id
FROM login
WHERE username = 'ruben'
AND password = 'p@ssw0rd'
String sql = "SELECT id FROM login " +
"WHERE username = '" + form.username + "'" +
"AND password = '" + form.password + "'";
// Now execute the SQL statement to get the id for the user
// (if the username and password are correct)
SELECT id
FROM login
WHERE username = 'administrator'
AND password = '' OR 'pwned' = 'pwned'
try {
con = DriverManager.getConnection(url, "scott", "tiger");
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery ();
while (rs.next ()) {
// . . . rs.getString(1) . . .
}
// DO NOT close resources here!
// (See finally clause on next page)
}
catch (SQLException e) {
// handle error, e.g., print message, terminate
// . . . e.getErrorCode() . . .
// . . . e.getSQLState() . . .
// . . . e.getMessage() . . .
}
finally {
if (rs != null) {
try {
rs.close ();
}
catch (SQLException e2) {
// handle it. . .
}
}
if (pstmt != null) {
try {
pstmt.close ();
}
catch (SQLException e2) {
// handle it. . .
}
}
if (con != null) {
try { con.close (); }
catch (SQLException e2) {
// handle it. . .
}
}
}
The DatabaseMetaData object gives information about the database system and the catalog
DatabaseMetaData md = con.getMetaData();
System.out.println ("Name: " + md.getDriverName());
System.out.println ("Version: " + md.getDriverVersion());
DatabaseMetaData md=con.getMetaData();
ResultSet trs=md.getTables(null,null,null,null);
String tableName;
while(trs.next()) {
tableName = trs.getString("TABLE_NAME");
System.out.println("Table: " + tableName);
ResultSet crs = md.getColumns(null,null,tableName, null);
while (crs.next()) {
System.out.println(" " + crs.getString("COLUMN_NAME"));
}
}
try {
con = DriverManager.getConnection(url, "login", "pass");
String query = "SELECT title, year FROM Movies";
stmt = con.prepareStatement(query);
rs = pstmt.executeQuery();
while (rs.next()) {
String title = rs.getString("title");
int year = rs.getInt("year");
System.out.println(title + " " + year);
}
}
catch(SQLException ex) {
System.out.println(ex.getMessage ()
+ ex.getSQLState () + ex.getErrorCode ());
}
finally {
if (rs != null) {
try {
rs.close ();
}
catch (SQLException e2) {
// handle it somehow
// e.g., log it or retry N times. . .
}
}
if (pstmt != null) {
try {
pstmt.close ();
}
catch (SQLException e2) {
// handle it somehow
}
}
if (con != null) {
try {
con.close ();
}
catch (SQLException e2) {
// handle it somehow
}
}
} // end finally
@Configuration
@Import({JettyConfiguration.class, SpringSecurityConfiguration.class})
@ComponentScan(basePackages = {"uwyo.cs.acm2013"},
excludeFilters = {@ComponentScan.Filter(Controller.class),
@ComponentScan.Filter(Configuration.class)})
public class RootConfiguration {
@Bean
public DataSource getDataSource() {
DataSource dbsrc = null;
try {
dbsrc = new SimpleDriverDataSource(new com.mysql.jdbc.Driver(),
"jdbc:mysql://localhost/test",
"root", "");
} catch (SQLException e) {
e.printStackTrace();
}
return dbsrc;
}
}
public class TestDAOImpl implements TestDAO {
private JdbcTemplate jdbcTemplate = null;
public TestDAOImpl(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
@Override
public List<Foo> getFoos() {
String sql = "SELECT a, b FROM foo";
List<Foo> foos = jdbcTemplate.query(sql, new RowMapper<Foo>() {
@Override
public Foo mapRow(ResultSet rs, int rowNum) throws SQLException {
Foo foo = new Foo(rs.getInt("a"), rs.getInt("b"));
return foo;
}
});
return foos;
}
@Override
public void deleteFoo(int a) {
String sql = "DELETE FROM foo WHERE a=?";
jdbcTemplate.update(sql, a);
}
}
int sid;
String name;
float gpa;
#sql iterator Students(int sid, String name, float gpa);
Students students;
#students = {
SELECT sname, gpa INTO :name, :gpa
FROM Students
WHERE sid = :sid
};
while (students.next()) {
System.out.println(students.name + " " + students.gpa));
}
students.close();
Session session = factory.getCurrentSession ();
session.beginTransaction();
Student student = new Student(9, "John Galt", 4.0);
session.save (student);
session.getTransaction().commit();
String hql = "from Student s where s.gpa <= :min_gpa";
Query query = session.createQuery ();
query.setFloat ("min_gpa", 2.0);
List<Student> students = query.list (hql);
// Note: List<Student> is a Java class
for (Student student : students) {
// Note: student is a POJO
// Now do something with student
}
Faculty f = . . .;
for (Student student : f.getAdvisees ()) {
System.out.println (student.getSname());
}