数据库连接

JNDI

* 1，配置 context.xml

* 2，配置 web.xml

* 3，进行代码编写，使用lookup() 方法获得数据源对象

package com.xxx.jndi;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.naming.NamingException;

import javax.sql.DataSource;

import com.xxx.entity.SysUser;

public class JNDItest {

public static java.sql.Connection getConnection(){ Connection conn=null;

try {

Context ic=new InitialContext();

System.out.println(ic+"ic ic ic ");

DataSource

source=(DataSource)ic.lookup("java:comp/env/jdbc/crm");

conn=source.getConnection();

} catch (NamingException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return conn;

}

public void testJNDI(){

String sql="select * from sys_user";

Connection conn=getConnection();

PreparedStatement pstmt=null;

ResultSet rs=null;

System.out.println(getConnection());

try {

pstmt=conn.prepareStatement(sql);

rs=pstmt.executeQuery();

System.out.println(rs);

while(rs.next()){

SysUser user=new SysUser();

user.setUserName(rs.getString("userName"));

System.out.println(user.getUserName());

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

System.out.println(getConnection());

}

/*

* 1，配置 context.xml

* 2，配置 web.xml

* 3，进行代码编写，使用lookup() 方法获得数据源对象

*

*

*

* 必要的项配置 context.xml

*

driverClassName="com.mysql.jdbc.Driver"

url="jdbc:mysql://localhost:3306/crm"

/>

2，配置 web.xml

jdbc/crm

javax.sql.DataSource

Container

3，进行代码编写，使用lookup() 方法获得数据源对象

全项

Context ic=new InitialContext();

System.out.println(ic+"ic ic ic ");

DataSource

source=(DataSource)ic.lookup("java:comp/env/jdbc/crm");

conn=source.getConnection();

*

*

*

*/

}

JDBC

package com.xxx.jdbc;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import com.xxx.entity.SysUser;

public class JDBCconnection {

private static final String driver_class="com.mysql.jdbc.Driver";

private static final String database_url="jdbc:mysql://localhost:3306/crm";

private static final String name="root";

private static final String password="root";

public static java.sql.Connection getConnection(){

java.sql.Connection conn=null;

try {

Class.forName(driver_class);

conn=DriverManager.getConnection(database_url, name, password);

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return conn;

}

public static void main(String [] args){

String sql="select * from sys_user";

Connection conn=getConnection();

PreparedStatement pstmt=null;

ResultSet rs=null;

try {

pstmt=conn.prepareStatement(sql);

rs=pstmt.executeQuery();

System.out.println(rs);

while(rs.next()){

SysUser user=new SysUser();

user.setUserName(rs.getString("userName"));

System.out.println(user.getUserName());

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

数据库

create database sunDB

on primary

(

name='sunDB_data',

Filename='E:\sun\作业\SQLServer_2\T1\sunDB_data.mdf',

size=5mb,

maxsize= 10mb,

filegrowth= 15%

),

(

name='sunDB_data2',

filename='E:\sun\作业\SQLServer_2\T1\sunDB_data.ndf',

size= 5,

maxsize=10,

filegrowth=15%

)

log on

(

name='sunDB_log1',

filename='E:\sun\作业\SQLServer_2\T1\sunDB_log1.ldf',

size= 5,

maxsize=10,

filegrowth=15%

)

create table student

(

StudentNo int primary key identity(100200,1),

StudentName varchar(20)unique not null,

StudentAge int check(StudentAge<=30 and StudentAge>=20), Country varchar(10)not null default('中国'),

StuTime datetime not null

)

create table course

(

Course_id int primary key identity(2000,1),

Course_naem varchar(10)unique not null,

Course_description varchar(50),

Cousre_score int not null

)

create table score

(

Score_id int primary key identity(100,1),

Stu_id int foreign key references student(StudentNo),

Course_id int foreign key references course(Course_id),

sscore int

)

insert into student values('张三',22,'中国',1993-3-1)

insert into student values('李四',22,'中国',1933-3-1)

insert into student values('王五',23,'中国',1942-3-1)

insert into student values('无花',22,'中国',1944-3-1)

insert into student(StudentName,StudentAge,StuTime)values('孙悟空',28,1955-3-1)

insert into student values('猪八戒',25,default,1922-3-1)

insert into course values('语文','必修',3)

insert into course values('数学','必修',3)

insert into course values('英语','必修',3)

insert into course values('java','必修',4)

insert into course values('Database','必修',3)

insert into course values('HTML','必修',3)

insert into score values(100200,2000,88)

insert into score values(100201,2000,55)

insert into score values(100203,2000,88)

insert into score values(100204,2000,56)

insert into score values(100205,2000,88)

insert into score values(100202,2000,46)

select identity(int,100,1) as Score_id,score.Stu_id,score.Course_id,score.sscore into

newscore1

from score

where sscore>60

drop table newscore1

select * from newscore1

SQL2005

private static final String

driver_class="com.microsoft.sqlserver.jdbc.SQLServerDriver";

p rivate static final String database_url="jdbc:sqlserver://localhost:1433;DatabaseName=sunDB";

存储过程

无参数的存储过程

if exists(select*from sysobjects where name='proc_stu') drop procedure proc_stu

go

create procedure proc_stu

as

declare @writtenAvg float,@labAvg float

select*from score

GO

exec proc_stu

调用

public void testCall(){

String sql;

sql = "{call proc_stu()}";

try{

conn = getConnection();

call = conn.prepareCall(sql);

call.execute();

rs = call.getResultSet();

while(rs.next()){

System.out.println(rs.getInt("sscore"));

}

}catch(SQLException e){

System.out.println("数据库操作异常！！");

e.printStackTrace();

}

}

带参数的存储过程

if exists(select*from sysobjects where name='proc_stu2') drop procedure proc_stu2

go

create procedure proc_stu2

@score int,

@maxscore int

as

select*from score where sscore>@score and sscore<@maxscore go

exec proc_stu2 60,90

调用

public void testCall2(){

sql = "{call proc_stu2(?,?)}";

try{

conn = getConnection();

call = conn.prepareCall(sql);

call.setInt(1, 60);

call.setInt(2, 90);

call.execute();

rs = call.getResultSet();

while(rs.next()){

System.out.println(rs.getInt("sscore"));

}

}catch(SQLException e){

System.out.println("数据库操作异常！！");

e.printStackTrace();

}

}

带有输出参数的存储过程

if exists(select*from sysobjects where name='proc_stu3') drop procedure proc_stu3

go

create procedure proc_stu3

@numbers int output,

@minscore int,

@maxscore int

as

select @numbers=count(sscore)from score where sscore>@minscore and sscore<@maxscore

select*from score where sscore>@minscore and sscore<@maxscore

go

declare @sum int

exec proc_stu3 @sum output,60,90

print'-----------------------------------------------------------' print'满足条件的人数为：'+convert(varchar(5),@sum)+'人'

go

调用

public void testCall3(){

sql = "{call proc_stu3(?,?,?)}";

try{

conn = getConnection();

call = conn.prepareCall(sql);

call.setInt(2, 60);

call.setInt(3, 90);

call.registerOutParameter(1, Types.INTEGER);

call.execute();

rs = call.getResultSet();

while(rs.next()){

System.out.println(rs.getInt("sscore"));

}

System.out.println("总共有"+call.getInt(1)+"满足上面的条件！！");

}catch(SQLException e){

System.out.println("数据库操作异常！！");

e.printStackTrace();

}

}