1、数据库:
mysql数据库,在mysql数据库中新建一个tbtest数据库,在tbtest库中新建person表。字段如下
人员表(姓名(字符串),手机(字符串),地址(字符串))
CREATE TABLE `person` (
`p_name` varchar(50) DEFAULT NULL,
`p_number` varchar(50) DEFAULT NULL,
`p_address` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
创建好表,自己添加一点数据
2、创建结果集:
2.1、要求:
1.存储过程名称:sp_test_get_persons
2.返回含有2个字段的结果集,一个字段是int类型的id,另外一个是varchar(100)类型的name
3.调用时,可以指定最大返回结果集中的数据个数。例如:call sp_test_get_persons(10) 调用后返回的结果集中最多10条记录。
2.1、Navicat创建结果集
保存,设置名字为:sp_test_get_persons
一个简单的结果集就ok了
3、需要的jar包:
1、maven项目在pom.xml中<dependencies>里加入这个:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.0.8</version>
</dependency>
2、或者 简单的java项目,在加入jar包,并导入项目依赖
mysql-connector-java5.0.8
4、新建java项目:
4.1、DBManager.java
创建jdbc工具类
package com.lemon.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBManager {
//数据库信息
private static final String url = "jdbc:mysql://localhost:3306/tbtest";
private static final String name = "com.mysql.jdbc.Driver";
private static final String username = "root";
private static final String password = "123456";
private static Connection connection = null;
private static PreparedStatement preparedStatement = null;
//创建连接
public DBManager(){
try{
Class.forName(name);
connection = DriverManager.getConnection(url, username, password);
}catch(Exception e){
e.printStackTrace();
}
}
//关闭连接
private void close(){
try{
connection.close();
preparedStatement.close();
}catch (Exception e){
e.printStackTrace();
}
}
//查询
public void queryAll() throws SQLException{
String sql = "select * from person";
preparedStatement = connection.prepareStatement(sql);
String name,number,address;
try
{
//执行sql语句
ResultSet result = preparedStatement.executeQuery();
//查询到数据,对应赋值
while(result.next()){
name = result.getString(1);
number = result.getString(2);
address = result.getString(3);
//显示
System.out.println(name + ",\t" + number + ",\t" + address + ".\t");
}
result.close();
}catch (Exception e){
e.printStackTrace();
}
}
//增加
public void addPerson(String name ,String number ,String address) throws SQLException{
String sql = "insert into person (p_name,p_number,p_address) values (?,?,?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, name);
preparedStatement.setString(2, number);
preparedStatement.setString(3, address);
int row = preparedStatement.executeUpdate();
if(row > 0){
System.out.println("添加成功!");
}
preparedStatement.close();
}
//删除
public void delPersonByName(String name) throws SQLException{
String sql = "delete from person where p_name = ?";
//
preparedStatement = connection.prepareStatement(sql);
//完整sql语句
preparedStatement.setString(1, name);
int row = preparedStatement.executeUpdate();
if(row > 0){
System.out.println("删除成功!");
}else{
System.out.println("请正确输入需要删除的姓名!");
}
preparedStatement.close();
}
//更新
public void updatePerson(String name ,String number ,String address) throws SQLException{
String sql = "update person set";
if(number != null){
sql = sql + " p_number = ?";
}
if(number != null && address != null){
sql = sql + ",";
}
if(address != null){
sql = sql + "p_address = ?";
}
sql = sql + "where p_name = ?";
//
preparedStatement = connection.prepareStatement(sql);
int i = 1;
//完整sql语句
if(number != null){
preparedStatement.setString(i, number);
i++;
}
if(address != null){
preparedStatement.setString(i, address);
i++;
}
preparedStatement.setString(i, name);
int row = preparedStatement.executeUpdate();
if(row > 0){
System.out.println("更新成功!");
}else{
System.out.println("请正确输入需要更新的姓名!");
}
preparedStatement.close();
}
}
4.2、test.java
测试类:
package com.lemon.test;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import javax.swing.DefaultButtonModel;
public class test {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Scanner input = new Scanner(System.in);
//实例化
DBManager dbManager = new DBManager();
//查询
//dbManager.queryAll();
//添加
//dbManager.addPerson("张三","123456789","上海");
//dbManager.queryAll();
//按照名字删除
//dbManager.delPersonByName("张三");
//按照名字更新
//dbManager.updatePerson("sss","123213","12321");
System.out.println("请输入返回的最大结果集");
int num = input.nextInt();
System.out.println(num);
//返回结果集
dbManager.printResult(num);
}
}