.


:

:



,



( , )

:

































 

 

 

 
FAQ

⇐ 35363738394041424344 ⇒



1. .

:

String driverName = "org.gjt.mm.mysql.Driver";

MySQL,

String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";

MSAccess

String driverName = "org.postgreesql.Driver";

PostgreeSQL.

:

Class.forName(driverName);

.

, . , DB2 IBM - :

new com.ibm.db2.jdbc.net.DB2Driver();

2. .


getConnection() DriverManager. URL , . Connection. URL , , . :

Connection cn = DriverManager.getConnection("jdbc:mysql://localhost/my_db", "root", "pass");

Connection my_db. DriverManager .
registerDriver() , getDrivers() .

3. .

Connection SQL. Statement, createStatement() Connection.

Statement st = cn.createStatement();

Statement SQL- . PreparedStatement CallableStatement . SQL,
executeQuery(String sql) executeUpdate(String sql).

4. .

ResultSet:

ResultSet rs = st.executeQuery(

"SELECT * FROM my_table"); // my_table

, executeQuery() executeUpdate().

5. ResultSet, next()
getString(int pos) , get (int pos) (getInt(int pos), getFloat(int pos) .) update (). getClob(int pos) getBlob(int pos), (Character Large Object, Binary Large Object), , , . .

next() . , false.

6.

Cn.close();

, .

, . .

MySQL

MySQL c JDBC . C www.mysql.com. . . root pass. , (
). /mysql/bin:

Mysqld-nt -standalone

, MySQL . SQL.

, MySQL

mysql-connector-java-3.1.12.jar,

/WEB-INF/lib .

db2 users. : name phone . , , .

/* # 1: : ServletToBase.java */

package chapt20;

import java.io.*;

import java.sql.*;

import javax.servlet.*;

import javax.servlet.http.*;

 

public class ServletToBase extends HttpServlet {

public void doGet(HttpServletRequest req,

HttpServletResponse resp)

throws ServletException {

performTask(req, resp);

}

public void doPost(HttpServletRequest req,

HttpServletResponse resp)

throws ServletException {

performTask(req, resp);

}

 

public void showInfo(PrintWriter out, ResultSet rs)

throws SQLException {

out.print("From DataBase:");

while (rs.next()) {

out.print("<br>Name:-> " + rs.getString(1)

+ " Phone:-> " + rs.getInt(2));

}

}

public void performTask(HttpServletRequest req,

HttpServletResponse resp) {

resp.setContentType("text/html; charset=Cp1251");

PrintWriter out = null;

try { //1

out = resp.getWriter();

try { //2

 

Class.forName( "org.gjt.mm.mysql.Driver" );

// MSAccess

/* return "sun.jdbc.odbc.JdbcOdbcDriver" */

// PostgreeSQL

/* return " org.postgreesql.Driver " */

Connection cn = null;

try { //3

cn =

DriverManager.getConnection( "jdbc:mysql://localhost/db2",

"root", "pass");

// MSAccess

/* return "jdbc:odbc:db2"; */

// PostgreeSQL

/* return "jdbc:postgreesql://localhost/db2"; */

Statement st = null;

try { //4

st = cn.createStatement();

ResultSet rs = null;

try { //5

rs = st.executeQuery( "SELECT * FROM users" );

out.print("From DataBase:");

while (rs.next()) {

out.print("<br>Name:-> " + rs.getString(1)

+ " Phone:-> " + rs.getInt(2));

}

} finally { // 5- try

/ * ResultSet,

*/

// ResultSet

if (rs!= null) rs.close();

Else

out.print(" ");

}

} finally { // 4- try

/* Statement,

ResultSet */

// Statement

if (st!= null) st.close();

else out.print("Statement ");

}

} finally { // 3- try

/* Connection,

ResultSet

Statement */

// - Connection

if (cn!= null) cn.close();

else out.print("Connection ");

}

} catch (ClassNotFoundException e) { // 2- try

out.print(" ");

}

}

/* SQLException IOException finally, */

/* catch */

catch (SQLException e) {

} // 1- try

catch (IOException e) {

} // 1- try

finally { // 1- try

/* PrintWriter,

*/

// , PrintWriter

if (out!= null) out.close();

Else

out.print("PrintWriter ");

}

}

}

, () .

database.properties, , , , . :

url =jdbc:mysql://localhost/my_db?useUnicode=true&

characterEncoding=Cp1251

driver =org.gjt.mm.mysql.Driver

user =root

password =pass

-,
, :

public Connection getConnection()

throws SQLException {

 

ResourceBundle resource =

ResourceBundle. getBundle ("database");

String url = resource.getString("url");

String driver = resource.getString("driver");

String user = resource.getString("user");

String pass = resource.getString("password");

try {

Class. forName (driver).newInstance();

} catch (ClassNotFoundException e) {

throw new SQLException(" !");

} catch (InstantiationException e) {

e.printStackTrace();

} catch (IllegalAccessException e) {

e.printStackTrace();

}

return DriverManager. getConnection (url, user, pass);

}

ResourceBundle, ,
getBundle(String filename), . , MissingResourceException. getString(String name), . ResourceBundle , getKeys(), Enumeration, . getObject(String key) getStringArray(String key) .

ResultSetMetaData
DatabaseMetaData . , , . .

ResultSetMetaData :

ResultSetMetaData rsMetaData = rs.getMetaData();

ResultSetMetaData:

int getColumnCount() ResultSet;

String getColumnName(int column) ResultSet;

int getColumnType(int column) ResultSet ..

DatabaseMetaData :

DatabaseMetaData dbMetaData = cn.getMetaData();

DatabaseMetaData:

String getDatabaseProductName() ;

String getDatabaseProductVersion() ;

String getDriverName() JDBC;

String getUserName() ;

String getURL() ;

ResultSet getTables() , , ..

PreparedStatement CallableStatement. SQL. , . , .

SQL-, , prepareStatement(String sql) Connection, PreparedStatement.
setString(), setInt() ,
executeUpdate(),
executeQuery(). , , . -
,
.

/* # 2: :

PreparedStatementServlet.java */

package chapt20;

import java.io.*;

import java.sql.*;

import javax.servlet.*;

import javax.servlet.http.*;

public class PreparedStatementServlet extends HttpServlet {

protected void doGet(HttpServletRequest req,

HttpServletResponse resp)

throws ServletException, IOException {

performTask(req, resp);

}

protected void doPost(HttpServletRequest req,

HttpServletResponse resp)

throws ServletException, IOException {

performTask(req, resp);

}

protected void performTask(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

resp.setContentType("text/html");

PrintWriter out = resp.getWriter();

try {

Class.forName("org.gjt.mm.mysql.Driver");

Connection cn = null;

try {

cn = DriverManager.getConnection("jdbc:mysql://localhost/db3","root","");

PreparedStatement ps = null;

String sql =

"INSERTINTO emp(id,name,surname,salary) VALUES(?,?,?,?)";

// ()

ps = cn.prepareStatement(sql);

Rec.insert(ps, 2203, "", "", 230);

Rec.insert(ps, 2308, "John", "Black", 450);

Rec.insert(ps, 2505, "Mike", "Call", 620);

out.println("COMPLETE");

} finally {

if (cn!= null) cn.close();

}

} catch (Exception e) {

e.printStackTrace();

}

out.close();

}

}

class Rec {

static void insert(PreparedStatement ps, int id, String name, String surname, int salary)

throws SQLException {

//

ps.setInt(1, id);

ps.setString(2, name);

ps.setString(3, surname);

ps.setInt(4, salary);

//

Ps.executeUpdate();

}

}

db3 COMPLETE.

CallableStatement PreparedStatement .

SQL, , , ( ). SQL, . . CallableStatement
prepareCall() Connection.

CallableStatement , . , CallableStatement (IN) , (OUT) (INOUT) . registerOutParameter(). execute(), executeQuery() executeUpdate().

getempname, employee SSN :

CREATE PROCEDURE getempname

(emp_ssn IN INT, emp_name OUT VARCHAR) AS

BEGIN

SELECT name

INTO emp_name

FROM employee

WHERE SSN = EMP_SSN;

END

employee java- :

String SQL = "{call getempname (?,?)}";

CallableStatement cs = conn.prepareCall(SQL);

cs.setInt(1,822301);

//

cs.registerOutParameter(2,java.sql.Types.VARCHAR);

cs.execute();

String empName = cs.getString(2);

System.out.println("Employee with SSN:" + ssn

+ " is " + empName);

:

Employee with SSN:822301 is Spiridonov

JDBC batch-, SQL , .

// turn off autocommit

con.setAutoCommit(false);

Statement stmt = con.createStatement();

stmt.addBatch("INSERT INTO employee VALUES

(10, 'Joe ')");

stmt.addBatch("INSERT INTO location VALUES

(260, 'Minsk')");

stmt.addBatch("INSERT INTO emp_dept VALUES

(1000, 260)");

// submit a batch of update commands for execution

int [] updateCounts = stmt.executeBatch();

PreparedStatement, batch- SQL- .

PreparedStatement.executeBatch() , , batch-.

Employee get()/set() , . execute() executeUpdate() , batch-:

try {

Employee[] employees = new Employee[10];

PreparedStatement statement =

con.prepareStatement("INSERT INTO employee VALUES

(?,?,?,?,?)");

for (int i = 0; i < employees.length; i++) {

Employee currEmployee = employees[i];

statement.setInt(1, currEmployee.getSSN());

statement.setString(2, currEmployee.getName());

statement.setDouble(3, currEmployee.getSalary());

statement.setString(4,currEmployee.getHireDate());

statement.setInt(5, currEmployee.getLoc_Id());

statement.addBatch();

}

updateCounts = statement.executeBatch();

} catch (BatchUpdateException e) {

e.printStackTrace();

}

,
-
. . , , , , , . , .
.

( ) , ACID:

. , .

. , .

-, , .

, , , , . .

SQL-, , SQL- COMMIT. API JDBC
executeQuery() executeUpdate(). COMMIT, setAutoCommit(boolean param) Connection false, . , COMMIT . SQL- commit() Connection, . , rollback() SQL, commit().
, , commit()
rollback().

<!-- # 3: : index.jsp -->

<%@ page language="java" contentType="text/html; charset=windows-1251" pageEncoding="windows-1251"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional //EN">

<html><head>

<meta http-equiv="Content-Type"

content="text/html; charset=windows-1251">

<title>Simple Transaction Demo</title>

</head>

<body>

<form name="students" method="POST"

action="SQLTransactionServlet" >

 

id: <br/>

<input type="text" name=" id " value=""><br/>

Name: <br/>

<input type="text" name=" name " value=""><br/>

 

Course: <br/>

<select name="course">

<option>Java SE 6

<option>XML

<option>Struts

</select><br/>

 

<input type="submit" value="Submit">

</form>

</body></html>

/* # 4: : perform()

SQLTransactionServlet.java */

public void taskPerform(HttpServletRequest request,

HttpServletResponse response)

throws ServletException, IOException {

response.setContentType("text/html; charset=Cp1251");

PrintWriter out = null;

Connection cn = null;

try {

out = response.getWriter();

String id = request.getParameter("id");

String name = request.getParameter("name");

String course = request.getParameter("course");

out.print("ID : " + id + ", " + name +<br>");

cn = getConnection();

cn.setAutoCommit(false);

Statement st = cn.createStatement();

try {

String upd;

upd =

"INSERT INTO student (id, name) VALUES ('"

+ id + "', '" + name + "')";

st.executeUpdate(upd);

out.print(" students: "

+ id + ", " + name + "<br>");

 

upd =

"INSERT INTO course(id_student, name_course) VALUES('"

+ id + "','" + course + "')";

st.executeUpdate(upd);

out.print(" course: " + id

+ ", " + course + "<br>");

 

cn.commit(); //

out.print("<b> - "

+ "</b><br>");

} catch (SQLException e) {

cn.rollback(); //

out.println("<b> :"

+ e.getMessage() + "</b>");

} finally {

if (cn!= null)

cn.close();

}

} catch (SQLException e) {

out.println("<b> :"

+ e.getMessage());

}

}

student course db1 , , ,

id nam   id_student name_course
  Goncharenko     Java SE 6

. 20.2.

:

id nam   id_student name_course
  Goncharenko     Java SE 6
  Petrov     XML

. 20.3.

ID : 83, Petrov
students: 83, Petrov
course: 83, XML
-

, Java.

:

(dirty reads) , . , , , , . , , , ;

(nonrepeatable reads) , , , ;

(phantom reads) , , WHERE -, , , , WHERE -, .

JDBC ,
SQL:2003.

Connection ( ):

TRANSACTION_NONE , ;

TRANSACTION_READ_UNCOMMITTED , , ;

TRANSACTION_READ_COMMITTED , , , , . , ;

TRANSACTION_REPEATABLE_READ , ;

TRANSACTION_SERIALIZABLE , , .

boolean supportsTransactionIsolationLevel(int level) DatabaseMetaData , .

, Connection :

int getTransactionIsolation() ;

void setTransactionIsolation(int level) .

. , . , , rollback() , .

boolean supportsSavepoints() DatabaseMetaData , , JDBC .

setSavepoint(String name) setSavepoint() ( Savepoint) Connection . , setSavepoint() .

/* # 5: : SavepointServlet.java */

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import java.io.IOException;

import java.io.PrintWriter;

import java.sql.*;

 

public class SavepointServlet extends HttpServlet {

protected void doGet(HttpServletRequest req,

HttpServletResponse resp)

throws ServletException, IOException {

performTask(req, resp);

}

protected void doPost(HttpServletRequest req,

HttpServletResponse resp)

throws ServletException, IOException {

performTask(req, resp);

}

protected void performTask(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

resp.setContentType("text/html; charset=Cp1251");

PrintWriter out = resp.getWriter();

try {

Class.forName("org.gjt.mm.mysql.Driver");

Connection cn = null;

Savepoint savepoint = null;

try {

cn = DriverManager.getConnection("jdbc:mysql://localhost/db3","root","pass");

cn.setAutoCommit(false);

out.print("<b> ...</b>");

out.print("<br>");

Statement stmt = cn.createStatement();

String trueSQL =

"INSERT INTO emp (id,name,surname,salary) "

+ "VALUES(2607,'','',540)";

stmt.executeUpdate(trueSQL);

//

savepoint =

cn.setSavepoint("savepoint1");

//

String wrongSQL =

" INSERT INTO (id,name,surname,salary) "

+ "VALUES(2607,'','',540)";

stmt.executeUpdate(wrongSQL);

} catch (SQLException ex) {

out.print(ex + "<br>");

Cn.rollback(savepoint);

out.print("<b> : "

+ savepoint + "</b>");

} finally {

if (cn!= null) cn.close();

}

} catch (Exception e) {

e.printStackTrace();

}

out.close();

}

}

:

...

java.sql.SQLException: You have an error in your SQL syntax...

: savepoint1

trueSQL .

, , . ( ) . (pool) , , .

( , ).

server.xml pache Tomcat :

<Context docBase=" FirstProject " path="/FirstProject"
reloadable="true" source="com.ibm.etools.webtools.server: FirstProject">

<! MySQL -->

<Resource auth="Container" name=" jdbc/db1 "

type=" javax.sql.DataSource "/>

<ResourceParams name=" jdbc/db1 ">

<parameter>

<name>factory</name>

<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>

</parameter>

<parameter>

<name> driverClassName </name>

<value> org.gjt.mm.mysql.Driver </value>

</parameter>

<!url- JDBC db1

autoReconnect=true, url- JDBC, , mysqld . mysqld 8 .-->

<parameter>

<name> url </name>

<value> jdbc:mysql://localhost:3306/db1?autoReconnect=true

</value>

</parameter>

<parameter>

<name> usernam e</name>

<value> root </value>

</parameter>

<parameter>

<name> password </name>

<value> pass </value>

</parameter>

<parameter>

<name> maxActive </name>

<value> 500 </value>

</parameter>

<parameter>

<name>maxIdle</name>

<value>10</value>

</parameter>

<parameter>

<name>maxWait</name>

<value>10000</value>

</parameter>

<parameter>

<name>removeAbandoned</name>

<value>true</value>

</parameter>

<parameter>

<name>removeAbandonedTimeout</name>

<value>60</value>

</parameter>

<parameter>

<name>logAbandoned</name>

<value>true</value>

</parameter>

</ResourceParams>

</Context>

JNDI.

, , DataSource javax.sql, J2EE . DataSource ,
.

InitialContext, JNDI API, . DataSource ( ,
), DataSource.

lookup() . lookup() , .

javax.naming.Context ct =

new javax.naming.InitialContext();

DataSource ds = (DataSource)ct.lookup("java:jdbc/db1");

Connection cn = ds.getConnection("root", "pass");

, :

cn.close();

DataSource. , , :

import COM.ibm.db2.jdbc.DB2DataSource;

...

DB2DataSource ds = new DB2DataSource();

ds.setServerName("//localhost:50000/db1");

Connection cn = ds.getConnection("db2inst1", "pass");

DB2DataSource.

20

:

, .

. .

.

.

HTML- .

HTML-.

1. . , , .

:

;

.

:

;

;

, .

().

, .

, .

.

.

.

2. . , , .

:

;

;

;

, .

:

;

.

, .

, .

, N .

, .

, .

3. .
.

:

;

( );

, .

:

;

, ;

;

, .

, .

,
.

, .

, .

.

4. . .

:

;

.

:

;

;

;

;

.

, .

, .

, .

, .

.

5. . .

:

;

;

;

.

:

;

.

.

, .

, .

, .

.

6. . .

:

;

;

.

:

;

.

:

;

;

.

.

, ,
.

, .

, .

, , .

, .

7. . .

:

;

(, .);

;

;

.

:

;

( .).

:

;

.

:

;

.

.

, .

.

.

, .

.

8. . .

:

;

;

;

.

:

;

;

.

:

;

.

.

, .

, .


.

9. . , .

:

;

(, );

;

;

.

:

;

.

.

, .

, .

,
.

10. . .

:

;

;

;

.

:

;

;

.

, .

, .


, .

.

11. . ,
.

:

;

;

;

;

;

.

:

;

;

.

:

;

.

, , .

, .

, , ,
, ,
.

, .

12. . .

, .

, .

, .

13. . .

, .

, .

, .

14. . - , . () . () .

15. . : - -. . .

16. . . Blob. .

?

?

?

17. . .

.

.

18. .
.

.

.

.

.

B

4 . ( ). .

20

20.1.

JDBC-
?

1) java.sql.DriverManager;

2) javax.sql.DataSource;

3) java.sql.Statement;

4) java.sql.ResultSet;

5) java.sql.Connection.

20.2.

java.sql JDBC?

1) Driver;

2) DriverManager;

3) Connection;

4) DriverPropertyInfo;

5) ResultSet.

20.3.

Connection , ?

1) getMetaData();

2) getDatabaseInfo();

3) getInfo();

4) getMetaInfo();

5) getDatabaseMetaData().

20.4.

java.sql , ?

1) Statement;

2) PreparedStatement;

3) StoredStatement;

4) CallableStatement;

5) StoredProcedure.

20.5.

Statement SQL- SELECT, ResulSet?

1) execute();

2) executeQuery();

3) executeUpdate();

4) executeBatch();

5) executeSelect();

6) executeSQL().

 
21

C,

()

Web- , . HTTP . c . , , . . . .

() , , . Web-. .

Web- , HTTP- (, -; ). , :

(/ , ..);

;

(expiration), (watchdog).

, getSession() HttpServletRequest. HttpSession, .
,
getCreationTime() getLastAccessedTime().

getSession(boolean param) true, - , . . :

HttpSession se = request.getSession(true);

.

, setAttribute() HttpSession, getAttribute(), removeAttribute(). , , , Enumeration getAttributeNames(), , HttpServletRequest.

String getId() , . isNew() false true .

, , :

se.setAttribute( " teacherId ", new Integer(71));

teacherId :

Integer testId = (Integer)se.getAttribute( " teacherID " );

invalidate(). , , , .

/* # 1: : SessionServlet.java */

package chapt21;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

 

public class SessionServlet extends HttpServlet {

protected void doGet(

HttpServletRequest req,

HttpServletResponse resp)

throws ServletException {

performTask(req, resp);

}

private void performTask(

HttpServletRequest req,

HttpServletResponse resp)

throws ServletException {

⇐ 35363738394041424344 ⇒


:

: 2016-04-03; !; : 820 |

:

:

: , , , , .
==> ...

1744 - | 1586 -


© 2015-2024 lektsii.org - -

: 0.784 .