JDBC程序设计 实验一（纯用作记录代码）

一、Navicat for MySQL命令行

CREATE TABLE `Query1` (
`sid`  int(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
`name`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`age`  int(11) NOT NULL ,
`score`   double NOT NULL,
PRIMARY KEY (`sid`)
);
INSERT INTO Query1(NAME,AGE,SCORE)VALUES('A',20,77);
INSERT INTO Query1(NAME,AGE,SCORE)VALUES('B',21,88);
INSERT INTO Query1(NAME,AGE,SCORE)VALUES('C',22,87);
INSERT INTO Query1(NAME,AGE,SCORE)VALUES('D',23,96);

二、Net Beans IDE 7.4

1.导入库 mysql-connector-java-5.1.7-bin.jar

链接：https://pan.baidu.com/s/1xJNC7fMLSDzZd2sK2JX5Wg 
提取码：1234

2.代码(类：User，userDao，Test1)

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package test1;

/**
 *
 * @author 15679
 */
public class User {
    private String sid;
    private String name;
    private Integer age;
    private Double score;


    public User(){

    }
    //全参构造方法
    public User(String sid, String name, Integer age, Double score) {
        this.sid = sid;
        this.name = name;
        this.age = age;
        this.score = score;
    }

    @Override
    public String toString(){
        return "Sid="+sid+"\tname="+name+"\tage="+age+"\tscrore="+score;
    }

    public Double getScore() {
        return score;
    }
     public void setScore(Double score) {
        this.score = score;
    }

    public String getSid() {
        return sid;
    }

    public void setSid(String sid) {
        this.sid = sid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

}

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package test1;

/**
 *
 * @author 15679
 */
import java.sql.*;
import java.sql.Connection;

public class userDao {
    public static PreparedStatement ps;
    public static Connection conn=null;
    public userDao(com.mysql.jdbc.Connection conn) {
        this.conn=  conn;
 }
   
//1 输出全部信息
    public void checkall() throws SQLException{
        //String sql="select *from query1";
        Statement ps= conn.createStatement();
        ResultSet rows=ps.executeQuery("select *from query1");
        try{
        while(rows.next()){
            String sid=rows.getString(1);
            String name=rows.getString(2);
            int age=rows.getInt(3);
            double score =rows.getDouble(4);
     
            System.out.println("sid:"+sid+",name:"+name+",age:"+age+",score:"+score);
        }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //2 根据Sid查找信息
    public User check(String checks){
        //String  sql="select sid,name,age,score from Users where sid = ?";
        try {
            ps=conn.prepareStatement("select sid,name,age,score from query1 where sid = ?");
            ps.setString(1,checks);

           //得到结果集
            ResultSet resultSet=ps.executeQuery();
            //处理结果集
            if(resultSet.next()){
                //判断结果集的下一条是否有数据，如果有数据返回true，并指针下移，如果返回false，指针不会下移
                //获取当前这条数据的各个字段值
                String id =resultSet.getString(1);
                String name=resultSet.getString(2);
                int age=resultSet.getInt(3);
                double score=resultSet.getDouble(4);
                //把属性封装在java类中来进行表示
                User user=new User(id,name,age,score);
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new User();
    }
//3 更新成绩
    public void modify(int score,String name){
      //String  sql="update Users set score=? where name=?";
        try {
            ps=conn.prepareStatement("update query1 set score=? where name=?");
            //填充占位符
            ps.setInt(1,score);
            ps.setString(2,name);
            //执行操作
            ps.execute();
            //资源的关闭
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
     //4 增加
    public void add(String sid,String name,int age,int score){
        //4.预编译sql语句，返回PreparedStatement的实例
        //String sql="insert into  query1(sid,NAME,age,score) values(?,?,?,?)";

        PreparedStatement ps= null;
        try {
            ps = conn.prepareStatement("insert into  query1(sid,name,age,score) values(?,?,?,?)");
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //填充占位符
        try {
            ps.setString(1,sid);
            ps.setString(2,name);
            ps.setInt(3,age);
            ps.setInt(4,score);
            //执行操作
            ps.execute();
            //资源的关闭
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
    }
    
    
    //5 删除
    public void delete(String sid){
      //String  sql="DELETE FROM query1 WHERE sid=?";
        try {
            PreparedStatement ps=conn.prepareStatement("delete from query1 WHERE sid=?");
            ps.setString(1,sid);
            ps.execute();
            //资源的关闭
            ps.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package test1;

/**
 *
 * @author 15679
 */
import com.mysql.jdbc.Connection;
import java.sql.DriverManager;

import java.util.Scanner;


public class Test1 {
    public static int flag_test;

    public static void main(String[] args) throws Exception {
        
        //user.login();
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn=(Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");
        userDao user = new userDao(conn);
        Scanner in = new Scanner(System.in);
        System.out.println("Enter your choice\n-1.list all students\n-2.find student  by its id\n-3.update a student\n-4.add a new student\n-5.delete an student by id\n-6.and program\n-?");
        System.out.println("请输入所需要进行操作:");
        int number = in.nextInt();
        String Sid;
        String name;
        int age;
        int score;
        switch (number) {
            case 1:
                user.checkall();
                break;
            case 2:
                in = new Scanner(System.in);
                System.out.println("请输入ID:");
                Sid = in.next();
                User users = user.check(Sid);
                System.out.println(users);
                break;
            case 3:
                System.out.println("请输入姓名:");
                name = in.next();
                System.out.println("请输入修改后分数:");
                score = in.nextInt();
                user.modify(score, name);
                break;
            case 4:
                in = new Scanner(System.in);
                System.out.println("请输入学号:");
                Sid = in.next();
                System.out.println("请输入姓名:");
                name = in.next();
                System.out.println("请输入年龄:");
                age = in.nextInt();
                System.out.println("请输入分数:");
                score = in.nextInt();
                user.add(Sid, name, age, score);
                break;
            case 5:
                System.out.println("请输入学号:");
                Sid = in.next();
                user.delete(Sid);
                break;
            default:
                System.out.println("未开发该选项");
                break;
        }
    }
}

三、数据库连接

1.新建连接 ("jdbc:mysql://localhost:3306/test","root","root")

2.新建数据库test

3.命令行创建表Query1