Java 类与JDBC用法

最新推荐文章于 2024-10-30 18:07:32 发布

denghui1855

最新推荐文章于 2024-10-30 18:07:32 发布

阅读量113

点赞数

文章标签： java 数据库

原文链接：http://www.cnblogs.com/invokerrr/p/10632388.html

版权

2019-03-31

import java.sql.SQLException;//捕获问题

import java.sql.Connection;//连接数据库
import java.sql.DriverManager;//DriverManager管理一组 JDBC 驱动程序的基本服务。DataSource 接口是 JDBC 2.0 API 中的新增内容，它提供了连接到数据源的另一种方法。使用 DataSource 对象是连接到数据源的首选方法。
import java.sql.PreparedStatement;//使用preparedStatement进行增删改查

public class JDBCUtil {
    public static Connection getConn() //定义静态方法  静态方法可以直接用调用  JDBCutil.getConn()
                                       //如Connection a = JDBCUtil.getconn();
                                       //而非静态方法 public aa()
                                       // 只能用 aa b = new aa（）;

    {
        Connection connection=null;
        try {
            Class.forName("com.mysql.jdbc.Driver"); //返回一个类
            String url ="jdbc:mysql://localhost:3306/grades?user=root&password=123";
            connection =DriverManager.getConnection(url);
         }
        catch (ClassNotFoundException e) {
            System.out.println("JDBC connection error");
        } catch (SQLException e) { //SQLException 用来捕获问题
            System.out.println("JDBC operation error");
        }
        return connection;
    }
    public static void closeConn(PreparedStatement preStmt,Connection conn)
    {
        if(preStmt!=null) {
            try
            {
                preStmt.close();//关闭链接
            }
            catch(SQLException e)
            {
                System.out.println("PreparedStatement close error");
            }
        }
        if(conn!=null) {
            try 
            {
                conn.close();//关闭连接避免占用过多资源
            }catch(SQLException e)
            {
                System.out.println("PreparedStatement close error");
            }
        }
    }
    
}

package cn.edu.hbue.xyl.gui;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;

public class InsertExample {
    
    public static void main(String[] args) {
        Connection connection =JDBCUtil.getConn();
        PreparedStatement preStmt =null;
        try {
            String sql ="insert into stock(orderId,sName,consignmentDate,baleName,count,money)values(?,?,?,?,?,?)";
            preStmt =connection.prepareStatement(sql);
            preStmt.setInt(1, 1235);
            preStmt.setString(2, "饼干");
            preStmt.setString(3, "2013-6-29");
            preStmt.setString(4, "小陈");
            preStmt.setInt(5, 20);
            preStmt.setDouble(6, 223);
            preStmt.executeUpdate();//返回更新的数据行数
            System.out.println("添加数据的行数为"+preStmt.getUpdateCount());
            
        }        catch (SQLException e) {
    System.out.println("JDBC operation error");
        }finally {
            JDBCUtil.closeConn(preStmt, connection);
        }
    }


}

其中execute 和 exeupDate 和 executeQuery区别参考 大佬：
https://www.cnblogs.com/angelye/p/7855906.html

import java.awt.Container;
import java.awt.FlowLayout;
import java.awt.HeadlessException;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JInternalFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JTextField;

public class InsertStockFrame extends JFrame implements ActionListener{
    private JTextField orderIdTextField; //JTextField表示的是文本框   详情https://zhidao.baidu.com/question/288180446.html
    private JTextField sNameTextField;
    private JTextField consignmentDateTextField;
    private JTextField baleNameTextField;
    private JTextField countTextField;
    private JTextField moneyTextField;
    private JLabel orderLabel;//JLabel 对象可以显示文本、图像或同时显示二者。  详情http://outofmemory.cn/code-snippet/1121/swing-JLabel-explain-in-detail-yiji-usage-shili
    private JLabel sNameLabel;
    private JLabel dateLabel;
    private JLabel baleNameLabel;
    private JLabel countLabel;
    private JLabel moneyLabel;
    private JLabel starLabel;
    private JButton confirmButton;
    private JButton cancelButton;
    
    public InsertStockFrame(){
        
        setTitle("进货");
        setSize(220, 300);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);//用户单击窗口的关闭按钮时程序执行的操作
        
        Container cp = this.getContentPane();//this.getContentPane()的作用是初始化一个容器，用来在容器上添加一些控件
        cp.setLayout(new FlowLayout());//setLayout是对当前组件设置为流式布局.组件在窗体中从左到右依次排列
        
        orderLabel = new JLabel("订单号");
        cp.add(orderLabel);
        orderIdTextField = new JTextField(10);
        cp.add(orderIdTextField);
        starLabel = new JLabel("*");
        cp.add(starLabel);
        
        baleNameLabel = new JLabel("商品名称");
        cp.add(baleNameLabel);
        baleNameTextField = new JTextField(10);
        cp.add(baleNameTextField);
        starLabel = new JLabel("*");
        cp.add(starLabel);
        
        dateLabel = new JLabel("交货日期");
        cp.add(dateLabel);
        consignmentDateTextField= new JTextField(10);
        cp.add(consignmentDateTextField);
        starLabel = new JLabel("*");
        cp.add(starLabel);
        
        sNameLabel = new JLabel("客    户");
        cp.add(sNameLabel);
        sNameTextField = new JTextField(10);
        cp.add(sNameTextField);
        starLabel = new JLabel("*");
        cp.add(starLabel);
        

        countLabel = new JLabel("数    量");
        cp.add(countLabel);
        countTextField = new JTextField(10);
        cp.add(countTextField);
        starLabel = new JLabel("*");
        cp.add(starLabel);
        
        moneyLabel = new JLabel("金   额");
        cp.add(moneyLabel);
        moneyTextField = new JTextField(10);
        cp.add(moneyTextField);
        starLabel = new JLabel("*");
        cp.add(starLabel);
        
        confirmButton = new JButton("添加");
        confirmButton.addActionListener(this);//  建立事件监听事件 https://blog.csdn.net/qq_41978199/article/details/80642012
        cp.add(confirmButton);
        cancelButton = new JButton("取消");
cancelButton.addActionListener(this);
        cp.add(cancelButton);
        
    }
    
    public void actionPerformed(ActionEvent e) { //actionPerformed 用于接收操作事件的侦听器接口
        if(e.getActionCommand().equals("添加")){
            //从各控件取出数据
            String orderId = orderIdTextField.getText();
            String sName = sNameTextField.getText();
            String date = consignmentDateTextField.getText();
            String baleName = baleNameTextField.getText();
            String count = countTextField.getText();
            String money = moneyTextField.getText();
            
            //此处补充代码
            //非空或必填字段检测
            if(orderId.equals("")){ //如果内容为空
                JOptionPane.showMessageDialog(getContentPane(), "请将带星号的内容填写完整！","信息提示框", JOptionPane.INFORMATION_MESSAGE);
                return ;
            }
            
            if(sName.equals("")){
                JOptionPane.showMessageDialog(getContentPane(), "请将带星号的内容填写完整！","信息提示框", JOptionPane.INFORMATION_MESSAGE);
                return ;
            }
            
            if(date.equals("")){
                JOptionPane.showMessageDialog(getContentPane(), "请将带星号的内容填写完整！","信息提示框", JOptionPane.INFORMATION_MESSAGE);
                return ;
            }

            

            if(baleName.equals("")){
                JOptionPane.showMessageDialog(getContentPane(), "请将带星号的内容填写完整！","信息提示框", JOptionPane.INFORMATION_MESSAGE);
                return ;
            }
            
            if(count.equals("")){
                JOptionPane.showMessageDialog(getContentPane(), "请将带星号的内容填写完整！","信息提示框", JOptionPane.INFORMATION_MESSAGE);
                return ;
            }
            
            if(money.equals("")){
                JOptionPane.showMessageDialog(getContentPane(), "请将带星号的内容填写完整！","信息提示框", JOptionPane.INFORMATION_MESSAGE);
                return ;
            }
            
            

            //此处补充代码
            //进货日期,数量,金额的合法性检验
            int c = 0;
            double m ;
            int flag = 0; //非常关键！！ 不然程序会重复输出
            
            try
            {
                c = Integer.parseInt(count);//Integer.parseInt(String)的作用就是将String字符类型数据转换为Integer整型数据
                m = Double.parseDouble(money);
            }
            catch(NumberFormatException t)
            {
                JOptionPane.showMessageDialog(getContentPane(), "请将带星号的内容填写完整！","信息提示框", JOptionPane.INFORMATION_MESSAGE);
                return ;
            }
            
            JDBCUtil genConnection = new JDBCUtil() ;
            Connection conn = genConnection.getConn();
            String sql = "insert into stock(orderId,sName,consignmentDate,baleName,count,money)"+
                        " values(?,?,?,?,?,?)";
            PreparedStatement preStmt = null;
            
            try {
                preStmt= conn.prepareStatement(sql);
                preStmt.setString(1, orderId);
                preStmt.setString(2, sName);
                preStmt.setString(3, date);
                preStmt.setString(4, baleName);
                preStmt.setInt(5, c);
                preStmt.setDouble(6, m);
                
                preStmt.executeUpdate();
                System.out.println("成功插入数据"+ preStmt.getUpdateCount() + "条");
                
            } catch (SQLException t) {
                flag = 1;
                t.printStackTrace();
            }finally
            {
                JDBCUtil.closeConn(preStmt, conn);
            }
            try {
                if (flag == 0)
                {
                    JOptionPane.showMessageDialog(getContentPane(), "插入成功","信息提示框", JOptionPane.INFORMATION_MESSAGE);
                    orderIdTextField.setText(""); //成功或失败以后将文本框清零
                    sNameTextField.setText("");
                    consignmentDateTextField.setText("");
                    baleNameTextField.setText("");
                    countTextField.setText("");
                    moneyTextField.setText("");
                }
            else
            {
                JOptionPane.showMessageDialog(getContentPane(), "插入失败","信息提示框", JOptionPane.INFORMATION_MESSAGE);
                orderIdTextField.setText(""); 
                sNameTextField.setText("");
                consignmentDateTextField.setText("");
                baleNameTextField.setText("");
                countTextField.setText("");
                moneyTextField.setText("");
        }
            }
       catch(HeadlessException t) 
        {
            t.printStackTrace();
        }
        }
        else
        {
         System.exit(0); //直接退出程序
        }
        }
            
    public static void main(String[] args){
        InsertStockFrame insert = new InsertStockFrame(); //调用前面方法
        insert.setVisible(true);
    }
}


实现如下

package cn.edu.hbue.xyl.gui;   


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;



public class get {
    
    public static void main(String[] args) {
    
    Connection conn = JDBCUtil.getConn();
    String sql = "select * from stock where consignmentDate= '2018-03-22' ";//where id = 1； 如果是日期得加上单引号''
    PreparedStatement preStmt = null;
    ResultSet rs = null;
    
    try {
    preStmt = conn.prepareStatement(sql);
    rs = preStmt.executeQuery(); //执行后返回代表查询结果的ResultSet对象。
    while(rs.next()) {
        System.out.println("订单号=" + rs.getInt(2));
        System.out.println("货品名称:" + rs.getString(3));
        System.out.println("交货日期:" + rs.getDate(4));
        System.out.println("数量:" + rs.getInt("count"));
        System.out.println("金额=" + rs.getDouble("money"));
    }
    }catch(SQLException e) {
        e.printStackTrace();
    }
    
    }
    
}

package cn.edu.hbue.xyl.gui;

import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.swing.table.AbstractTableModel;

public class AllDataValues extends AbstractTableModel { //JTable用法 https://blog.csdn.net/edc3001/article/details/83386713
	Object[][] values ;
	String[] headerNames = {"订单号", "客户" ,"交货日期", "货物名称", "数量","金额"};
	public AllDataValues(){
		getValues();
	}
	
	private void getValues(){
		
		Connection conn = JDBCUtil.getConn();
		PreparedStatement stmt= null;
		ResultSet rs = null;
		PreparedStatement preStmt = null; //使用 preparedStatement对象 preStmt来进行增删改查
		ArrayList list ;//多维数组
		String sql = "select * from stock ";
		String countSql = "select count(id) from stock";  
		try {
			stmt = conn.prepareStatement(countSql);
			rs = stmt.executeQuery();//执行查询；要用statement类的executeQuery()方法来下达select指令以查询数据库，executeQuery()方法会把数据库响应的查询结果存放在ResultSet类对象中供我们使用。即语句：ResultSet rs=s.executeQuery(sql);
			int rows = 0;
			while(rs.next())
			   rows = rs.getInt(1);
			values = new Object[rows][];
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			int i =0;
			while(rs.next()){
				list = new ArrayList();// 动态数组 有点类似于set<string> https://www.cnblogs.com/rickie/articles/67978.html
				String orderId = rs.getString("orderId");
				list.add(orderId);
				String sName = rs.getString("sName");
				list.add(sName);
				java.sql.Date consignmentDate = rs.getDate("consignmentDate");
				list.add(consignmentDate);
				String baleName = rs.getString("baleName");
				list.add(baleName);
				Integer count = rs.getInt("count");
				list.add(count);
				Double money = rs.getDouble("money");
				list.add(money);
				
				values[i] = new Object[list.size()];
				values[i] = list.toArray();
				i++;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.closeConn(preStmt, conn);
		}
	}
	
	public int getColumnCount() {
		return values[0].length;
	}

	public int getRowCount() {
		return values.length;
	}

	public Object getValueAt(int row, int col) {
		return values[row][col];
	}

	public String  getColumnName(int col){
		return headerNames[col];
	}
}

package cn.edu.hbue.xyl.gui; //调用前面方法

import java.awt.Container;
import java.awt.FlowLayout;

import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTable;

public class ShowDataFrame extends JFrame {
protected JTable table;

public ShowDataFrame(){
setSize(500,200);
setTitle("货物一览表");
Container pane = getContentPane();
pane.setLayout(new FlowLayout());
AllDataValues dv = new AllDataValues();
table = new JTable(dv);
pane.add(new JScrollPane(table));
this.pack();
}
public static void main(String[] args){
ShowDataFrame s = new ShowDataFrame();
s.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
s.setSize(500,200);
s.setVisible(true);
}
}

一般情况下程序直接写在 main方法底下
当有extends或者implements其他接口的时候用public 写比如: public Insert（）然后在底下的main方法中调用 insert如： insert a = new insert（）；
写其他的方法时候如 actionperformed 时候用public void
JFrame是调用窗口指令记得最后要加上关闭窗口和setvisib（true）不然窗口不可见或者无法关闭

转载于:https://www.cnblogs.com/invokerrr/p/10632388.html