public class Base {
/**
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
template();
}
static void template() throws Exception {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
// 2.建立连接
conn = JdbcUtils.getConnection();
// conn = JdbcUtilsSing.getInstance().getConnection();
// 3.创建语句
st = conn.createStatement();
// 4.执行语句
rs = st.executeQuery("select * from user");
// 5.处理结果
while (rs.next()) {
// 参数中的1,2,3,4是指sql中的列索引
System.out.println(rs.getObject(1) + "/t" + rs.getObject(2)
+ "/t" + rs.getObject(3) + "/t" + rs.getObject(4));
}
} finally {
JdbcUtils.free(rs, st, conn);
}
}
static void test() throws SQLException, ClassNotFoundException {
// 1.注册驱动
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
System.setProperty("jdbc.drivers", "com.mysql.jdbc.Driver");
Class.forName("com.mysql.jdbc.Driver");// 推荐方式
// 2.建立连接
String url = "jdbc:mysql://localhost:3306/jdbc";
String user = "root";
String password = "";
Connection conn = DriverManager.getConnection(url, user, password);
// 3.创建语句
Statement st = conn.createStatement();
// 4.执行语句
ResultSet rs = st.executeQuery("select * from user");
// 5.处理结果
while (rs.next()) {
System.out.println(rs.getObject(1) + "/t" + rs.getObject(2) + "/t"
+ rs.getObject(3) + "/t" + rs.getObject(4));
}
// 6.释放资源
rs.close();
st.close();
conn.close();
}
}
============================================
public final class JdbcUtils {
private static String url = "jdbc:mysql://localhost:3306/jdbc";
private static String user = "root";
private static String password = "";
private JdbcUtils() {
}
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
public static void free(ResultSet rs, Statement st, Connection conn) {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (st != null)
st.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
======================================
public final class JdbcUtilsSing {
private String url = "jdbc:mysql://localhost:3306/jdbc";
private String user = "root";
private String password = "";
// private static JdbcUtilsSing instance = new JdbcUtilsSing();
private static JdbcUtilsSing instance = null;
private JdbcUtilsSing() {
}
public static JdbcUtilsSing getInstance() {
if (instance == null) {
synchronized (JdbcUtilsSing.class) {
if (instance == null) {
instance = new JdbcUtilsSing();
}
}
}
return instance;
}
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
public Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
public void free(ResultSet rs, Statement st, Connection conn) {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (st != null)
st.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
=========================================
public class BlobTest {
/**
* @param args
* @throws IOException
* @throws SQLException
*/
public static void main(String[] args) throws SQLException, IOException {
// create();
read();
}
static void read() throws SQLException, IOException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
// 2.建立连接
conn = JdbcUtils.getConnection();
// conn = JdbcUtilsSing.getInstance().getConnection();
// 3.创建语句
st = conn.createStatement();
// 4.执行语句
rs = st.executeQuery("select big_bit from blob_test");
// 5.处理结果
while (rs.next()) {
// Blob blob = rs.getBlob(1);
// InputStream in = blob.getBinaryStream();
InputStream in = rs.getBinaryStream("big_bit");
File file = new File("IMG_0002_bak.jpg");
OutputStream out = new BufferedOutputStream(
new FileOutputStream(file));
byte[] buff = new byte[1024];
for (int i = 0; (i = in.read(buff)) > 0;) {
out.write(buff, 0, i);
}
out.close();
in.close();
}
} finally {
JdbcUtils.free(rs, st, conn);
}
}
static void create() throws SQLException, IOException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 2.建立连接
conn = JdbcUtils.getConnection();
// conn = JdbcUtilsSing.getInstance().getConnection();
// 3.创建语句
String sql = "insert into blob_test(big_bit) values (?) ";
ps = conn.prepareStatement(sql);
File file = new File("IMG_0002.jpg");
InputStream in = new BufferedInputStream(new FileInputStream(file));
ps.setBinaryStream(1, in, (int) file.length());
// 4.执行语句
int i = ps.executeUpdate();
in.close();
System.out.println("i=" + i);
} finally {
JdbcUtils.free(rs, ps, conn);
}
}
}
===============================================
public class UserDaoJdbcImpl implements UserDao {
public void addUser(User user) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "insert into user(name,birthday, money) values (?,?,?) ";
ps = conn.prepareStatement(sql);
ps.setString(1, user.getName());
ps.setDate(2, new java.sql.Date(user.getBirthday().getTime()));
ps.setFloat(3, user.getMoney());
ps.executeUpdate();
} catch (SQLException e) {
throw new DaoException(e.getMessage(), e);
} finally {
JdbcUtils.free(rs, ps, conn);
}
}
public void delete(User user) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "delete from user where id=" + user.getId();
st.executeUpdate(sql);
} catch (SQLException e) {
throw new DaoException(e.getMessage(), e);
} finally {
JdbcUtils.free(rs, st, conn);
}
}
public User findUser(String loginName, String password) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
User user = null;
try {
conn = JdbcUtils.getConnection();
String sql = "select id, name, money, birthday from user where name=?";
ps = conn.prepareStatement(sql);
ps.setString(1, loginName);
rs = ps.executeQuery();
while (rs.next()) {
user = mappingUser(rs);
}
} catch (SQLException e) {
throw new DaoException(e.getMessage(), e);
} finally {
JdbcUtils.free(rs, ps, conn);
}
return user;
}
public User getUser(int userId) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
User user = null;
try {
conn = JdbcUtils.getConnection();
String sql = "select id, name, money, birthday from user where id=?";
ps = conn.prepareStatement(sql);
ps.setInt(1, userId);
rs = ps.executeQuery();
while (rs.next()) {
user = mappingUser(rs);
}
} catch (SQLException e) {
throw new DaoException(e.getMessage(), e);
} finally {
JdbcUtils.free(rs, ps, conn);
}
return user;
}
private User mappingUser(ResultSet rs) throws SQLException {
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setMoney(rs.getFloat("money"));
user.setBirthday(rs.getDate("birthday"));
return user;
}
public void update(User user) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "update user set name=?, birthday=?, money=? where id=? ";
ps = conn.prepareStatement(sql);
ps.setString(1, user.getName());
ps.setDate(2, new java.sql.Date(user.getBirthday().getTime()));
ps.setFloat(3, user.getMoney());
ps.setInt(4, user.getId());
ps.executeUpdate();
} catch (SQLException e) {
throw new DaoException(e.getMessage(), e);
} finally {
JdbcUtils.free(rs, ps, conn);
}
}
}
======================================
public class DaoFactory {
private static UserDao userDao = null;
private static DaoFactory instance = new DaoFactory();
private DaoFactory() {
try {
Properties prop = new Properties();
InputStream inStream = DaoFactory.class.getClassLoader()
.getResourceAsStream("daoconfig.properties");
prop.load(inStream);
String userDaoClass = prop.getProperty("userDaoClass");
Class clazz = Class.forName(userDaoClass);
userDao = (UserDao) clazz.newInstance();
} catch (Throwable e) {
throw new ExceptionInInitializerError(e);
}
}
public static DaoFactory getInstance() {
return instance;
}
public UserDao getUserDao() {
return userDao;
}
}
=========================================
public class DaoException extends RuntimeException {
/**
*
*/
private static final long serialVersionUID = 1L;
public DaoException() {
// TODO Auto-generated constructor stub
}
public DaoException(String message) {
super(message);
// TODO Auto-generated constructor stub
}
public DaoException(Throwable cause) {
super(cause);
// TODO Auto-generated constructor stub
}
public DaoException(String message, Throwable cause) {
super(message, cause);
// TODO Auto-generated constructor stub
}
}
=========================================