澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

jdbc简单使用,2005存储过程的写法

2019-08-31 作者:数据库网络   |   浏览(189)

开发数据库的SQL Server Managerment Studio---->数据库----->打开数据库会看见"可编制程序行"------->展开有囤积进度------->新建存款和储蓄进度

Statement和prepareStatement

jdbc中的Statement对象和Preparedstatement对象的区分,以及经过jdbc操作调用存款和储蓄进程,preparedstatement

一、

java.sql.*   和  javax.sql.*的包的类组织

 

                            |- Driver接口: 表示java驱动程序接口。全部的现实的数据库商家要来实现此接口。

                                     |- connect(url, properties):  连接数据库的法子。

                                                        url: 连接数据库的U汉兰达L

                                                                 U冠道L语法: 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 Server二零零六中
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(rsjdbc简单使用,2005存储过程的写法。!=null)
{
rs.close();
}
if(ps!=null)
{
ps.close();
}
}catch(SQLException e)
{
e.printStackTrace();
throw new RuntimeException(e);
}
}
}

1.test1()方法是调用未有重返值的蕴藏进程

2,test2()方法是调用有再次来到值的积累进度

在调用有再次来到值的存款和储蓄进度时,不可能使用executeQuery(),不然会报未有回到结果集的一无所长澳门新萄京官方网站 1

而改为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...

jdbc的粗略利用,jdbc轻松利用

1.加载驱动(mysql的驱动是com.mysql.jdbc.Driver,SqlServer的驱动是 com.microsoft.sqlserver.jdbc.SQLServerDriver)

2.加载数据库的一连(url, username,password)

3.编写sql语句(String sql="select * from grade  where gradeName = ?";)

4.遍历查询结果 【while (resultSet.next()) {
   System.out.println(resultSet.getInt("gradeId") "  " resultSet.getString("gradeName"));
  }】

5.停息财富(从后迈加入关贸总协定协会闭财富 )

 

public class Test {
public static void main(String[] args) {
 Connection connection = null;
 PreparedStatement preparedStatement = null;
 ResultSet resultSet = null;
 try {
  // 加载数据库驱动
  Class.forName("com.mysql.jdbc.Driver");
  // 通过驱动处理类获取数据库链接
  connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?characterEncoding=utf-8", "root", "root");
  // 定义sql语句 ?表示占位符
  String sql = "select * from grade  where gradeName = ?";
  // 获取预管理statement
  preparedStatement = connection.prepareStatement(sql);
  // 设置参数,第二个参数为sql语句中参数的序号(从1初步),第二个参数为设置的参数值
  preparedStatement.setString(1, "二年级");
  // 向数据库发出sql实行查询,查询出结果集
  resultSet = preparedStatement.executeQuery();
  // 遍历查询结果集
  while (resultSet.next()) {
   System.out.println(resultSet.getInt("gradeId") "  " resultSet.getString("gradeName"));
澳门新萄京官方网站,  }
 } catch (Exception e) {
jdbc简单使用,2005存储过程的写法。  e.printStackTrace();
 } finally {
  // 释放财富
  if (resultSet != null) {
   try {
    resultSet.close();
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
  if (preparedStatement != null) {
   try {
    preparedStatement.close();
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
  if (connection != null) {
   try {
    connection.close();
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
 }
}
}

1.加载驱动(mysql的驱动是com.mysql.jdbc.Driver,SqlServer的驱动是 com.microsoft.sqlserver.jdbc.SQLServerDriver) 2.加载数额库...

        

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("更新时间(update提姆e):" 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();

 

}

}

 

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:jdbc简单使用,2005存储过程的写法

关键词: