CREATE TABLE
CREATE TABLE. :
CREATE TABLE < >
(
< > < >[(< >)] [< >]
[, < > < >[(< >)] [< >]]
[, ]
[< >]
[,< >]
[, ]
)
:
CREATE TABLE Departments
(
DeptNum int NOT NULL PRIMARY KEY,
Name varchar(80) NOT NULL
)
CREATE TABLE Employees
(
TabNum int NOT NULL PRIMARY KEY,
Name varchar(100) NOT NULL,
Position varchar(200),
DeptNum int,
Salary decimal(10, 2) DEFAULT 0,
CONSTRAINT FK_DEPARTMENT FOREIGN KEY (DeptNum)
REFERENCES Departments(DeptNum)
)
CREATE TABLE, SELECT:
SELECT [ DISTINCT ] < >
INTO < >
FROM < > [ JOIN < > ON < >]
[ WHERE < >]
[ GROUP BY < > [ HAVING < >] ]
[ ORDER BY < >]
INTO SELECT , . SELECT .
ALTER TABLE
ALTER TABLE. ALTER TABLE , , , .
ALTER TABLE:
ALTER TABLE Departments ADD COLUMN City int
ALTER TABLE Departments DROP COLUMN City
ALTER TABLE Departments ADD
CONSTRAINT FK_City
FOREIGN KEY (City)
REFERENCES Cities(City)
ALTER TABLE Departments DROP CONSTRAINT FK_City
DROP TABLE
DROP TABLE:
DROP TABLE Departments
HIBERNATE
Hibernate - (Object-Relational Mapping, ORM) Java-. Hibernate , , . SQL-, Java- SQL Java-.
1. MySQL http://dev.mysql.com/
2. Hibernate
|
|
|
http://hibernate.org/
3. JDBC-,
tomcat/common/lib (, Ant, lib ), mysql-connector-java-3.1.12.jar. JDBC- .
hibernate3.1.3.jar . , Hibernate,
:
cglib.jar, commons-collections.jar, commons-logging.jar, jta.jar, dom4j.jar, log4j.jar, antlr.jar, asm.jar, asm-attrs.jar - Apache Ant.
dom4j.jar XML- XML-mapping ;
CGLIB (cglib.jar) ;
commons-collections.jar Apache Jakarta Commons;
commons-logging.jar Apache Jakarta Commons;
ODMG4 (odmg.jar) , mapping-;
EHCache (ehcache.jar) - ;
ANother Tool for Language (antlr.jar) , , , Java, C#, C++ Python ;
ASM (asm.jar, asm-attrs.jar) , , .
Hibernate JDBC- SQL- , JDBC- , Hibernate (C3P0, Proxool). Tomcat , DBCP , Hibernate JNDI. Tomcat JNDI, Tomcat 5.5/conf/server.xml, :
<Context path="/test_db" docBase="test_db">
<Resource name="jdbc/test_db" scope="Shareable"
type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/test_db">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory </value>
</parameter>
<Context/>
Hibernate .
WEB-INF/classes hibernate.cfg.xml.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="connection.datasource"> java:comp/env/jdbc/test_db</property>
<property name="show_sql">true</property>
<property name="dialect"> net.sf.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.connection.password">pass</property>
<property name="hibernate.connection.username">root</property>
|
|
|
<mapping resource="courses/hiber/Course.hbm.xml"/>
<mapping resource="courses/hiber/Student.hbm.xml"/> </session-factory>
</hibernate-configuration>
JDBC-, SQL, SQL.
Hibernate XML- hibernate.properties WEB-INF/ classes ( , , , ):
## ##
##
hibernate.query.substitutions true 1, false 0, yes 'Y',
no 'N'
## JDBC- MySQL
hibernate.dialect net.sf.hibernate.dialect.MySQLDialect
hibernate.connection.driver_class com.mysql.jdbc.Driver
##
##
## :
##hibernate.connection.url ##jdbc:mysql://_:_/__? ##autoReconnect= ## , . ## true, .. ## 30- .
## useUnicode=true
## unicode .
## characterEncoding=Cp1251 ##
hibernate.connection.url jdbc:mysql://localhost:3306/test_db?autoReconnect =true&useUnicode=true&characterEncoding=Cp1251
##
hibernate.connection.username root
##
hibernate.connection.password pass
##
## 50
##
hibernate.connection.pool_size 50
## . ## ##
hibernate.statement_cache.size 20
##, debug. ## , ,
##, ( ) .
hibernate.show_sql true
( ) , java.util.Properties. , :
private Properties createProperties() {
Properties properties = new Properties();
properties.setProperty(
"hibernate.dialect",
"net.sf.hibernate.dialect.MySQLDialect");
properties.setProperty(
"hibernate.connection.driver_class",
"com.mysql.jdbc.Driver");
properties.setProperty(
"hibernate.connection.url",
"jdbc:mysql://localhost/test_db");
properties.setProperty(
"hibernate.connection.username", "root");
properties.setProperty(
"hibernate.connection.password", "pass");
return properties;
}
POJO-
, , , POJO (Plain Ordinary Java Objects):
Course (
, );
Student (, ).
, id. . , ,
- , java.lang.String
java.util.Date. - , Hibernate .
/* # 1: POJO- : Course.java */
package courses.hiber;
import java.util.Set;
|
|
|
public class Course {
private Integer id;
private String title;
private Set students;
public Integer getId() {
return id;
}
protected void setId(Integer id) { /* protected , , mapping-. Hibernate , (friendly, public, protected, private)*/
this. id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this. title = title;
}
public Set getStudents() {
return students;
}
public void setStudents(Set students) {
this. students = students;
}
}
/* # 2: POJO- : Student.java */
package courses.hiber;
public class Student {
private Integer id;
private String lastname;
private String login;
private String password;
public Integer getId() {
return id;
}
protected void setId(Integer id) {
this. id = id;
}
public String getLogin() {
return login;
}
public void setLogin(String login) {
this. login = login;
}
public String getLastname() {
return lastname;
}
public void setLastname(String lastname) {
this. lastname = lastname;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this. password = password;
}
}
POJO- . - mapping XML- hbm.xml , *.class : Tomcat - WEB-INF\classes\_, Ant - bin\_. , Hibernate , . mapping- Java, , mapping java-, .
/* # 3: Mapping- courses.hiber.Course */
<?xml version="1.0 "?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
< hibernate-mapping >
< class name= "courses.hiber.Course" table= "course" >
< id name= "id" column= "id" type= "java.lang.Integer" >
< generator class= "increment"/ >
< /id >
< property name= "title" type= "java.lang.String" />
< set name= "students" table= "course_student"
cascade= "all" >
< key column= "course_id" />
< many-to-many column= "student_id" class= "courses.hiber.Student" />
</ set >
< /class >
< /hibernate-mapping >
/* # 4: Mapping- courses.hiber.Student */
<? xml version ="1.0"?>
<! DOCTYPE hibernate-mapping PUBLIC "- //Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
< hibernate-mapping package = " courses.hiber ">
< class name=" Student " table=" student ">
< id name ="id" column ="id" type="java.lang.Integer" >
< generator class="native"/>
< /id >
< property name ="lastname" type ="java.lang.String" />
|
|
|
< property name ="login" type ="java.lang.String" />
< property name ="password" type ="java.lang.String" />
< /class >
< /hibernate-mapping >
:
<class name="courses.hiber.Course"> , . : , package < hibernate - mapping > ;
table="course" , . , , .. COURSE, table ;
<id name="id" column="id" type="java.lang.Integer">
<generator class="native"/>
</id> , , .. , null. <generator> , : increment, identity, sequence, hilo,, seqhilo, uuid, guid, native, assigned, select, foreign;
<property name="title" column="column" type="java.lang.String"/>
. mapping- id. , , SQL-. .
, :
< many-to-one name= "propertyName" column= "column_name" class= "ClassName" lazy= "proxy|no-proxy|false" > (). , , , , . name column . class , . lazy , .. , , proxy, , , , .
< set name=" propertyName " inverse= "true" >
< key column= "foreignId"/ >
< one-to-many class= "ClassName"/ >
< /set > - .
mapping-.
mapping- , .. .
Hibernate, .
