数据库连接

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 ");DataSourcesource=(DataSource)ic.lookup("java:comp/env/jdbc/crm");conn=source.getConnection();} catch (NamingException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.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 blocke.printStackTrace();}System.out.println(getConnection());}/** 1，配置 context.xml* 2，配置 web.xml* 3，进行代码编写，使用lookup() 方法获得数据源对象**** 必要的项配置 context.xml* <Resource name="jdbc/crm" type="javax.sql.DataSource" username="root" password="root"driverClassName="com.mysql.jdbc.Driver"url="jdbc:mysql://localhost:3306/crm"/>2，配置 web.xml<resource-ref><res-ref-name>jdbc/crm</res-ref-name><res-type>javax.sql.DataSource</res-type><res-auth>Container</res-auth></resource-ref>3，进行代码编写，使用lookup() 方法获得数据源对象全项Context ic=new InitialContext();System.out.println(ic+"ic ic ic ");DataSourcesource=(DataSource)ic.lookup("java:comp/env/jdbc/crm");conn=source.getConnection();<!--<Resource name="jdbc/crm" auth="Container" type="javax.sql.DataSource" maxActive="100" maxIdle="30" maxWait="10000" username="root" password="root"driverClassName="com.mysql.jdbc.Driver"url="jdbc:mysql://localhost:3306/crm" />-->****/}JDBCpackage 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 blocke.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.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 blocke.printStackTrace();}}}数据库create database sunDBon 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 intonewscore1from scorewhere sscore>60drop table newscore1select * from newscore1SQL2005private static final Stringdriver_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_stugocreate procedure proc_stuasdeclare @writtenAvg float,@labAvg floatselect*from scoreGOexec 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_stu2gocreate procedure proc_stu2@score int,@maxscore intasselect*from score where sscore>@score and sscore<@maxscore goexec 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_stu3gocreate procedure proc_stu3@numbers int output,@minscore int,@maxscore intasselect @numbers=count(sscore)from score where sscore>@minscore and sscore<@maxscoreselect*from score where sscore>@minscore and sscore<@maxscoregodeclare @sum intexec proc_stu3 @sum output,60,90print'-----------------------------------------------------------' 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();}}。