Java链接数据库读取、存储基本操作

Java连接数据库

前注：sql字符串中，要向数据库中插入 字符串、日期，其两边是需要加上单引号的

向数据库中新建一个表：

try {
            DataBase database=new DataBase();
            database.connectSqlServer();
            
            String sql="DROP TABLE thingbase;"
                    +" CREATE TABLE thingbase(Thing_ID  CHAR(17) NOT NULL UNIQUE,"  //物品编号
                                            + "Name      CHAR(30) NOT NULL,"//物品名称
                                            + "Price     MONEY NOT NULL ,"//物品价格 
                                            + "Note      CHAR(50),"//物品备注
                                            + "Picture   IMAGE NOT NULL,"//物品图片
                                            + "PRIMARY KEY(Thing_ID));";
            PreparedStatement thingTable=DataBase.connection.prepareStatement(sql);
            //thingTable.setString(1, "Number  INTEGER");
            //thingTable.setString(2, "Name    CHAR(30) NOT NULL");
            //thingTable.setString(3, "Price   NOT NULL DOUBLE");
            //thingTable.setString(4, "Note    CHAR(50)");
            //thingTable.setString(5, "Picture IMAGE NOT NULL");
            thingTable.execute();//执行
            thingTable.close();
            System.out.println("创建表thingbase成功");
            
        } catch (Exception ex) {
            System.out.println("连接数据库失败");
            Logger.getLogger(CreateTable.class.getName()).log(Level.SEVERE, null, ex);
        }

注意：新建时不能使用PreparedStatement类中的？进行提前占位

连接并注册数据库代码

String url="jdbc:sqlserver://LAPTOP5FN1E5JD;integratedSecurity=true;database=SharedPlatFrom";
String name="com.microsoft.sqlserver.jdbc.SQLServerDriver";

    public void connectSqlServer() throws Exception{
        //注册驱动
        Class.forName(name);
        System.out.println("找到驱动类");
        connection=(Connection)DriverManager.getConnection(url);
        System.out.println("SQLServer连接成功："+connection);
    }
    

使用PraparedStatement向已连接数据库中指定的表录入数据

（可使用？提前进行占位进行预编译，随后使用.set+“/*录入数据类型*/”（/*位置*/，/*需要录入的数据*/）进行操作）

        try{
            File file =thing.getPicture();
            byte[] b = new byte[(int) file.length()];
            FileInputStream fis = new FileInputStream(file);
            fis.read(b);
            String sql = "INSERT INTO thingbase(thing_id,name,price,note,picture) VALUES(?,?,?,?,?)";
            try (PreparedStatement pStatement = connection.prepareStatement(sql)) {
                pStatement.setString(1, thing.getThing_id());
                pStatement.setString(2, thing.getName());
                pStatement.setDouble(3, thing.getPrice());
                pStatement.setString(4, thing.getNote());
                pStatement.setBytes (5, b);
                pStatement.execute();//执行
            }
        }catch (FileNotFoundException e) {
            System.out.println("文件未赋值");
            e.printStackTrace();
        }catch (IOException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        } 
        System.out.println("上传成功");
        JOptionPane.showMessageDialog(null,"上传成功","通知",JOptionPane.PLAIN_MESSAGE);

使用PreparedStatement从已连接数据库指定的表中读取数据

        Information thing=new Information();
        try {
            Statement statement = connection.createStatement();
            String Query="SELECT * FROM thingbase WHERE"+where;
            ResultSet rs = statement.executeQuery(Query);
            byte[] bb ;
            while (rs.next()) {
                File file = new File("e:\\a3.jpg");
                bb = rs.getBytes(5);//通过列号得到
                FileOutputStream fos = new FileOutputStream(file);
                fos.write(bb);
                thing.setPicture(file);
                thing.setThing_id(rs.getString(1));
                thing.setName(rs.getString(2));
                thing.setPrice(rs.getDouble(3));
                thing.setNote(rs.getString(4));
                System.out.println("数据读取完成");
                new TestMain().init(thing);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
    }