jdbc操作数据库的代码案例

来源：互联网发布：数据库基础知识编辑：程序博客网时间：2024/06/08 06:49

下面直接贴代码吧，实体类我就不贴了

获取数据库连接的工具类

package util;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class DBUtil {    // 驱动名称 这里是sqlserver驱动    private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";    // 数据库地址 127.0.0.1表示本地，    //1433是sqlserver默认的端口，food_db是要链接的数据库    private static final String URL = "jdbc:sqlserver://127.0.0.1:1433;databasename=food_db";    // 用户名    private static final String USER = "admin";    // 密码    private static final String PASS = "123456";    //在静态块中加载数据库连接驱动    static {        try {            Class.forName(DRIVER);        } catch (ClassNotFoundException e) {            e.printStackTrace();        }    }    //得到数据库连接的方法    public static Connection getConnection() {        Connection conn = null;        try {            conn = DriverManager.getConnection(URL, USER, PASS);        } catch (SQLException e) {            e.printStackTrace();        }        return conn;    }    //关闭连接的方法    public static void closeAll(Connection conn, Statement stmt, ResultSet rs) {        try {            if (rs != null)                rs.close();            if (stmt != null)                stmt.close();            if (conn != null)                conn.close();        } catch (SQLException e) {            e.printStackTrace();        }    }    //测试 打印下数据库连接是否正常得到    public static void main(String[] args) {        System.out.println(getConnection());    }}

//通过JdbcTemplate操作的方式

package dao.impl;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Date;import java.util.List;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowMapper;import util.DBUtil;import bean.Account;import bean.Trade;public class BankDaoImpl  {    //得到JdbcTemplate对象，调用DBUtil.getDataSource()得到数据库连接    private JdbcTemplate temp = new JdbcTemplate(DBUtil.getDataSource());    //查询指定条件的数据，Account实体类我就不写了    public Account login(Account a) {        //需要执行的sql语句 下面的Account实体类我就不写了        String sql = "select * from account where AccountID=? and password=? ";        List list = this.temp.query(sql, new Object[] { a.getAccountID(),a.getPassword() },                 new RowMapper() {                    public Object mapRow(ResultSet rs, int arg1) throws SQLException {                        Account a = new Account();                        a.setRemaining(rs.getDouble("remaining"));//获取字段名为remaining的数据                        a.setAccountID(rs.getString("accountid"));//获取字段名为accountid的数据                        a.setPassword(rs.getString("password"));//获取字段名为password的数据                        return a;                    }                });        if (list.size() > 0){            return (Account) list.get(0);        }        return null;    }    //查询多个对象方法，Trade跟Account实体类我就不写了    public List<Trade> selectByTime(String a, Date time1, Date time2) {        String sql = "select * from trade where accountid=? and TradeTime between ? and ? ";        return this.temp.query(sql, new Object[] { a, time1, time2 },                new RowMapper() {                    public Object mapRow(ResultSet rs, int arg1)throws SQLException {                        Trade t = new Trade();                        t.setId(rs.getInt("id"));                        Account a = new Account();                        a.setAccountID(rs.getString("accountid"));                        t.setAccount(a);                        t.setTradeDigest(rs.getString("tradeDigest"));                        t.setTradeMoney(rs.getDouble("tradeMoney"));                        t.setTradeTime(rs.getDate("tradeTime"));                        t.setTradeType(rs.getString("tradeType"));                        return t;                    }                });    }    //查询单个对象方法，Account实体类我就不写了    public Account selectRemaining(String aid) {        String sql = "select * from Account where accountid=?";        List list= this.temp.query(sql,new Object[] { aid },                 new RowMapper() {                    public Object mapRow(ResultSet rs, int arg1)                            throws SQLException {                        Account a = new Account();                        a.setAccountID(rs.getString("accountid"));                        a.setRemaining(rs.getDouble("remaining"));                        return a;                    }                });        if(list.size()==0){            return null;        }        return (Account) list.get(0);    }    //修改方法,返回受影响的行数    public int trade(String aid, double money) {        String sql = "update account set remaining=remaining+? where accountid=? ";        return this.temp.update(sql, new Object[] { money, aid });    }    //新增方法,返回受影响的行数(seq_trede.nextval数据库序列,sysdate获取当前系统时间)    public int insertTrade(Trade t) {        String sql = "insert into trade values(seq_trede.nextval,?,?,?,sysdate,?)";        return this.temp.update(sql, new Object[] {t.getAccount().getAccountID(),                 t.getTradeType(),t.getTradeMoney(), t.getTradeDigest() });    }}

//不通过JdbcTemplate对象操作

package util;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;public class Template {    // 针对任何表的查询    public void query(String sql, Object[] params) {        Connection conn = null;        PreparedStatement pst = null;        ResultSet rs = null;        conn = DBUtil.getConnection();//调用上面的DBUtil类中getConnection()方法获取链接        try {            pst = conn.prepareStatement(sql);            if (params != null) {                for (int i = 0; i < params.length; i++) {                    pst.setObject(i + 1, params[i]);                }            }            rs = pst.executeQuery();            //元数据类型            ResultSetMetaData rsmd = rs.getMetaData();            // 得到结果集共有几列            int count = rsmd.getColumnCount();            while (rs.next()) {// 遍历行                for (int i = 0; i < count; i++) {                    //我这里在控制台打印下,可以根据自己的需求封装                    System.out.print(rs.getObject(i + 1) + ",");                }                System.out.println();//打印换行            }        } catch (SQLException e) {            e.printStackTrace();        } finally {            DBUtil.closeAll(conn, pst, rs);        }    }    // 针对任何表的增，改，删，返回受影响的行数    // sql是要执行的SQL语句    // Object[]保存的是SQL语句要用到的参数值    public int update(String sql, Object[] params) {        int rows = 0;//记录受影响的行数        Connection conn = null;        PreparedStatement pst = null;        conn = DBUtil.getConnection();//调用上面的DBUtil类中getConnection()方法获取链接        try {            pst = conn.prepareStatement(sql);            if (params != null) {                for (int i = 0; i < params.length; i++) {                    pst.setObject(i + 1, params[i]);                }            }            rows = pst.executeUpdate();        } catch (SQLException e) {            e.printStackTrace();        } finally {            DBUtil.closeAll(conn, pst, null);        }        return rows;    }}

0 0