server的两种常见方法,以及通过jdbc操作调用存储
打开数据库的SQL Server Managerment Studio---->数据库----->打开数据库会看见"可编程行"------->打开有存储过程------->新建存储过程
Statement和prepareStatement
jdbc中的Statement对象和Preparedstatement对象的区别,以及通过jdbc操作调用存储过程,preparedstatement
一、
java.sql.* 和 javax.sql.*的包的类结构
|- Driver接口: 表示java驱动程序接口。所有的具体的数据库厂商要来实现此接口。
|- connect(url, properties): 连接数据库的方法。
url: 连接数据库的URL
URL语法: jdbc协议:数据库子协议://主机:端口/数据库
user: 数据库的用户名
password: 数据库用户密码
|- DriverManager类: 驱动管理器类,用于管理所有注册的驱动程序
|-registerDriver(driver) : 注册驱动类对象
|-Connection getConnection(url,user,password); 获取连接对象
|- Connection接口: 表示java程序和数据库的连接对象。
|- Statement createStatement() : 创建Statement对象
|- PreparedStatement prepareStatement(String sql) 创建PreparedStatement对象
|- CallableStatement prepareCall(String sql) 创建CallableStatement对象
|- Statement接口: 用于执行静态的sql语句
|- int executeUpdate(String sql) : 执行静态的更新sql语句(DDL,DML)
|- ResultSet executeQuery(String sql) :执行的静态的查询sql语句(DQL)
|-PreparedStatement接口:用于执行预编译sql语句
|- int executeUpdate() : 执行预编译的更新sql语句(DDL,DML)
|-ResultSet executeQuery() : 执行预编译的查询sql语句(DQL)
|-CallableStatement接口:用于执行存储过程的sql语句(call xxx)
|-ResultSet executeQuery() : 调用存储过程的方法
|- ResultSet接口:用于封装查询出来的数据
|- boolean next() : 将光标移动到下一行
|-getXX() : 获取列的值
Statemetent对象执行的是静态SQL语句,而PreparedStatement对象执行的是预编译SQL语句,如上图,Statement对象执行executeUpdate(String sql)和executeQuery(String sql),而PreparedStatement 对象执行的是无参的executeUpdate()和executeQuery(),从这两个方法可以看出这两个对象的特点,正因为如此,PreparedStatement可以预防SQL语句注入,更安全,当然它的效率也更高一些。
二、通过jdbc代码调用存储过程
代码如下
package com.a_callrablestatement;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.junit.Test;
import com.util.DBUtil;
public class Demo1 {
public Connection conn = null;
public CallableStatement cs = null;
ResultSet rs = null;
String driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://localhost:1433;DatabaseName=User";
String username = "sa";
String password = "qwer1234";
String sql = "exec pro_findById ? ";
public Demo1() {
// TODO Auto-generated constructor stub
}
@Test
public void test1()
{
try{
//注册驱动
Class.forName(driverClass);
//连接
conn = DriverManager.getConnection(url,username,password);
//得到prepareCall预编译对象
cs = conn.prepareCall(sql);
//设置问号的占位符
cs.setInt(1,3);
rs = cs.executeQuery();
//打印结果
while(rs.next())
{
int id = rs.getInt("id");
String name = rs.getString("username");
String password = rs.getString("password");
String gender = rs.getString("gender");
String interest = rs.getString("interest");
System.out.println(id+","+name+","+password+","+gender+","+interest);
}
}catch(ClassNotFoundException e)
{
e.printStackTrace();
}catch(SQLException e)
{
e.printStackTrace();
}finally
{
DBUtil.close(conn,rs,cs);
}
}
@Test
public void test2()
{
sql ="exec pro_findNameById ?,?";
try{
//注册驱动
Class.forName(driverClass);
//连接
conn = DriverManager.getConnection(url,username,password);
//得到prepareCall预编译对象
cs = conn.prepareCall(sql);
//设置问号的占位符的参数值
cs.setInt(1,3);
/**
* 1.参数一,表示要设置的参数位置
* 2.参数二,表示要返回的参数值类型 varchar(20)
*/
cs.registerOutParameter(2, Types.VARCHAR);
//执行操作,但不返回结果集,返回值在参数中,这里只能用execute(),不能用executeQuery(),这是在SQL
Server2008中
cs.execute();
/**
* 预编译sql中参数的位置
*/
String name = cs.getString(2);
//打印结果
System.out.println(name);
}catch(ClassNotFoundException e)
{
e.printStackTrace();
}catch(SQLException e)
{
e.printStackTrace();
}finally
{
DBUtil.close(conn,rs,cs);
}
}
}
工具类
package com.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBUtil {
public DBUtil() {
// TODO Auto-generated constructor stub
}
public static void close(Connection conn,ResultSet rs,PreparedStatement
ps )
{
try{
if(conn!=null)
{
conn.close();
}
if(rs!=null)
{
rs.close();
}
if(ps!=null)
{
ps.close();
}
}catch(SQLException e)
{
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
1.test1()方法是调用没有返回值的存储过程
2,test2()方法是调用有返回值的存储过程
在调用有返回值的存储过程时,不能使用executeQuery(),否则会报没有返回结果集的错误
而改为execute()方法后就可以得到正常的结果
test1()中的存储过程代码
use [User]
go
create procedure pro_findById(@id int)
as
select * from [tb_user] where
[email protected]
go
test2()中的存储过程
use [User]
go
create procedure findNameById @id int,@name varchar(20)
as
select @name=username from tb_user where
[email protected]
go
注:SQL server 2008和jdk 1.7 加eclipse ee 4.5
一、 java.sql.* 和 javax.sql.*的包的类结构 |- Dri...
sql server中已建立BookPhone数据库,包含bookPhone表,eclipse中有BookPhone类,三个string类型的值
实例:
1.
a.(数据库端)
import java.sql.Connection;
CREATE PROCEDURE [dbo].[get]
@hyzk varchar(255),
@feibie varchar(255)
AS
BEGIN
SELECT
hyzk,feibie,sysID,updateTime,chuanghao,ksmc FROM BingRenYiLan
WHERE hyzk=@hyzk AND feibie =@feibie
END
import java.sql.DriverManager;
b.(java后台调用sql server存储过程):
import java.sql.PreparedStatement;
//测试
import java.sql.ResultSet;
public static void main(String[] args) throws SQLException {
Connection con = null;
ResultSet rs = null;
// 调用数据库连接
con = getConnection();
//调用存储过程的语句-------------------
SQLServerCallableStatement call = (SQLServerCallableStatement) con
.prepareCall("{call get(?,?)}");
import java.sql.SQLException;
//设置参数
call.setString(1, "已婚");
call.setString(2, "自费");
//查询结果
rs = call.executeQuery();
public class jdbcDemo01 {
//遍历
while (rs.next()) {
System.out.println("婚姻状况(hyzk):" + rs.getString(1));
System.out.println("费别(feibie):"+ rs.getString(2));
System.out.println("主键(sysID):" + rs.getString(3));
System.out.println("更新时间(updateTime):" + rs.getString(4));
System.out.println("床号(chuanghao):"+ rs.getString(5));
System.out.println("科室名称(ksmc):" + rs.getString(6));
// System.out.println();
}
}
private final static String URL = "jdbc:sqlserver://127.0.0.1:1433;databasename=BookPhone";
// 初始化数据库连接参数
public static Connection getConnection() throws SQLException {
// 初始化变量
Connection con = null;
String url =
"jdbc:sqlserver://localhost:1433;databasename=CMCF_qqqqqqqk";
String userName = "sa";
String password = "wang";
private final static String user = "sa";
// 连接url
try {
// 加载数据库驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(url, userName, password);
} catch (SQLException e) {
con.close();
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
private final static String password = "123456";
static void insert(PhoneBook pb){
Connection conn=null;
PreparedStatement ps=null;
try{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection(URL, user, password);
String sqlString="insert into bookPhone(ph_name,ph_sex,ph_age) values(?,?,?)";
ps=conn.prepareStatement(sqlString);
ps.setString(1,pb.getName());
ps.setString(2,pb.getSex());
ps.setString(3,pb.getAge());
ps.executeUpdate();
ps.close();
conn.close();
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch(ClassNotFoundException e){
e.printStackTrace();
}
}
static void update(PhoneBook pb,String oldname){
Connection conn=null;
PreparedStatement ps=null;
try{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection(URL, user, password);
String sqlString="update bookPhone set ph_name=?,ph_sex=?,ph_age=? where ph_name=?";
ps=conn.prepareStatement(sqlString);
ps.setString(1,pb.getName());
ps.setString(2,pb.getSex());
ps.setString(3,pb.getAge());
ps.setString(4,oldname);
ps.executeUpdate();
ps.close();
conn.close();
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch(ClassNotFoundException e){
e.printStackTrace();
}
}
static void delete(String name){
Connection conn=null;
PreparedStatement ps=null;
try{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection(URL, user, password);
String sqlString="delete bookPhone where ph_name=?";
ps=conn.prepareStatement(sqlString);
ps.setString(1,name);
ps.executeUpdate();
ps.close();
conn.close();
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch(ClassNotFoundException e){
e.printStackTrace();
}
}
static PhoneBook requestByName(String name){
Connection conn=null;
PreparedStatement ps=null;
PhoneBook pb=null;
try{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection(URL, user, password);
String sqlString="select * from bookPhone where ph_name=?";
ps=conn.prepareStatement(sqlString);
ps.setString(1,name);
ResultSet rs=ps.executeQuery();
while(rs.next()){
pb=new PhoneBook();
pb.setName(rs.getString(1));
pb.setSex(rs.getString(2));
pb.setAge(rs.getString(3));
System.out.print(rs.getString(2)+",");
System.out.print(rs.getString(3));
}
rs.close();
ps.close();
conn.close();
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch(ClassNotFoundException e){
e.printStackTrace();
}
return pb;
}
public static void main(String[] args) {
PhoneBook pb1=new PhoneBook("王大毛","男","14");
PhoneBook pb2=new PhoneBook("王小毛","男","14");
insert(pb1);
System.out.println(requestByName("王大毛"));
delete("王大毛");
}
}
2.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcDemo {
private final static String URL="jdbc:sqlserver://localhost:1433;database=BookPhone";
private final static String user="sa";
private final static String password="123456";
static void insert(){
String name="李狗蛋";
String sex="男";
String age="12";
try{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection conn=DriverManager.getConnection(URL,user,password);
String sqlString="insert into bookPhone(ph_name,ph_sex,ph_age)"
+"values("+"'"+name+"','"+sex+"','"+age+"')";
Statement stmt=conn.createStatement();
stmt.executeUpdate(sqlString);
stmt.close();
conn.close();
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
}
static void update(PhoneBook pb,String oldName){
String name=pb.getName();
String sex=pb.getSex();
String age=pb.getAge();
try{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection conn=DriverManager.getConnection(URL,user,password);
String sqlString="update bookPhone set ph_name='"+name+"',ph_sex='"+sex+"',ph_age='"+age+"' where ph_name='"+oldName+"'";
Statement stmt=conn.createStatement();
stmt.executeUpdate(sqlString);
stmt.close();
conn.close();
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
}
static void delete(String name){
try{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection conn=DriverManager.getConnection(URL,user,password);
String sqlString="delete bookPhone where ph_name='"+name+"'";
Statement stmt=conn.createStatement();
stmt.executeUpdate(sqlString);
stmt.close();
conn.close();
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
}
static void request(){
try{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection conn=DriverManager.getConnection(URL,user,password);
String sqlString="select * from bookPhone";
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery(sqlString);
while(rs.next()){
System.out.print(rs.getString(1)+",");
System.out.print(rs.getString(2)+",");
System.out.println(rs.getString(3));
}
System.out.println();
stmt.close();
conn.close();
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
}
public static void main(String[] args) {
PhoneBook pb=new PhoneBook("李小狗蛋","男","12");
insert();
update(pb,"李狗蛋");
delete("barry55");
request();
}
}
本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:server的两种常见方法,以及通过jdbc操作调用存储
关键词:
上一篇:没有了
下一篇:没有了