jdbc操作数据库的代码案例

最新推荐文章于 2024-05-08 20:28:17 发布

周梦顺

最新推荐文章于 2024-05-08 20:28:17 发布

阅读量551

点赞数

分类专栏： JAVA 文章标签： jdbc

本文链接：https://blog.csdn.net/zhoumengshun/article/details/71775032

版权

JAVA 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

下面直接贴代码吧，实体类我就不贴了
需要引入的包:

<!-- mysql驱动包 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.20</version>
</dependency>

<!-- oracle驱动包 -->
<dependency>
     <groupId>com.oracle</groupId>
     <artifactId>ojdbc7</artifactId>
     <version>12.2.0.1</version>
</dependency>

获取数据库连接的工具类

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";
	//mysql驱动
	//private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
	//oracle驱动
	//private static final String DRIVER = "oracle.jdbc.OracleDriver";


	// 数据库地址 127.0.0.1表示本地，
    //1433是sqlserver默认的端口，food_db是要链接的数据库
	private static final String URL = "jdbc:sqlserver://127.0.0.1:1433;databasename=food_db";
	//mysql连接
	//private static final String URL = "jdbc:mysql://mysql数据库IP地址:mysql数据库端口/数据库名?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&tinyInt1isBit=false&allowMultiQueries=true&serverTimezone=GMT%2B8";
	 //oracle连接
	//private static final String URL = "jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = oracle数据库IP地址)(PORT = oracle数据库端口))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = oracle数据库服务名称)))";
	
	// 用户名
	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;
		
	}



}