import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import javax.swing.ImageIcon;
import javax.swing.JFrame;
import javax.swing.JLabel;
public class StudentDAO {
private Connection con = null;
private final String url = "jdbc:mysql://localhost:3306/uf_demo_db";
private final String userName = "root";
private final String password = "pass";
private Connection getConnection() {
try {
Class.forName("com.mysql.jdbc.Driver");
con = java.sql.DriverManager.getConnection(url, userName, password);
if (con != null)
System.out.println("Connection Successful!");
} catch (Exception e) {
e.printStackTrace();
System.out.println("Error Trace in getConnection() : "
+ e.getMessage());
}
return con;
}
public void displayDbProperties() {
java.sql.DatabaseMetaData dm = null;
java.sql.ResultSet rs = null;
try {
con = this.getConnection();
System.out.println(con.getClass().getName());
if (con != null) {
dm = con.getMetaData();
System.out.println("Driver Information");
System.out.println("\tDriver Name: " + dm.getDriverName());
System.out
.println("\tDriver Version: " + dm.getDriverVersion());
System.out.println("\nDatabase Information ");
System.out.println("\tDatabase Name: "
+ dm.getDatabaseProductName());
System.out.println("\tDatabase Version: "
+ dm.getDatabaseProductVersion());
System.out.println("Avalilable Catalogs ");
rs = dm.getCatalogs();
while (rs.next()) {
System.out.println("\tcatalog: " + rs.getString(1));
}
rs.close();
rs = null;
closeConnection();
} else {
System.out.println("Error: No active Connection");
}
} catch (Exception e) {
e.printStackTrace();
}
dm = null;
}
private void closeConnection() {
try {
if (con != null)
con.close();
con = null;
} catch (Exception e) {
e.printStackTrace();
}
}
public void addStudent(Student student) {
Connection con = getConnection();
try {
con.setAutoCommit(false);
String queryString = "insert into student values(?,?)";
PreparedStatement ps = con.prepareStatement(queryString);
ps.setInt(1, 3);
ps.setString(2, "new1");
int i = ps.executeUpdate();
System.out.println("影锟斤拷锟斤拷" + i + "锟斤拷");
queryString = "insert into student values(?,?,?)";
ps = con.prepareStatement(queryString);
ps.setInt(1, 3);
ps.setString(2, "new1");
ps.setString(3, "232");
i = ps.executeUpdate();
System.out.println("影锟斤拷锟斤拷" + i + "锟斤拷");
} catch (Exception e) {
try {
con.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
try {
con.commit();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public Student queryByName(String stuName) {
Student student = null;
ResultSet resultSet = null;
Connection con = getConnection();
String queryString = "select * from student where name=?";
// PreparedStatement ps = con.prepareStatement(queryString);
// ps.setString(1, stuName);
// resultSet = ps.executeQuery();
// if(resultSet.next()){
// student = new Student();
// student.setId(resultSet.getInt("id"));
// student.setName(resultSet.getString(2));
// }
// con.close();
return student;
}
public Student queryByNameAndId(String stuName, int id) throws Exception {
Student student = null;
ResultSet resultSet = null;
Connection con = getConnection();
String queryString = "select * from student where name=? and id=?";
PreparedStatement ps = con.prepareStatement(queryString);
ps.setString(1, stuName);
ps.setInt(2, id);
resultSet = ps.executeQuery();
if (resultSet.next()) {
student = new Student();
student.setId(resultSet.getInt("id"));
student.setName(resultSet.getString(2));
}
con.close();
return student;
}
/*
* if exists (select * from sysobjects where name='test' and type='p') drop
* procedure test go
*
* create procedure test @x int as select * from customers where id = @x
*/
public void countStudent() throws Exception {
String callSQL = "{call countStudent(?,?)}";
Connection con = getConnection();
CallableStatement cstmt = con.prepareCall(callSQL);
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setString(2, "test1");
cstmt.execute();
System.out.println("count is:" + cstmt.getInt(1));
con.close();
}
public List<Student> queryAllStudent() throws Exception {
List<Student> students = new ArrayList<Student>();
String callSQL = "{call queryAllStudent()}";
Connection con = getConnection();
CallableStatement cstmt = con.prepareCall(callSQL);
// cstmt.execute();
if (false == cstmt.execute()) {
System.out.println("execute error");
return students;
}
ResultSet rs = cstmt.getResultSet();
ResultSetMetaData rsmd = rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
System.out.print(rsmd.getColumnName(i) + "\t");
}
System.out.println("\n----------------------------------------");
while (rs.next()) {
System.out.print(rs.getInt("id") + "\t");
System.out.println(rs.getString("name") + "\t");
Student student = new Student();
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
students.add(student);
}
rs.close();
con.close();
return students;
}
public void testSaveAndGetImage() throws Exception {
Connection con = getConnection();
String insertSQL = "Insert INTO imagelist VALUES(?, ?)";
con.setAutoCommit(false);
PreparedStatement pstmt = con.prepareStatement(insertSQL);
File file = new File("boy.gif");
FileInputStream fis = new FileInputStream(file);
pstmt.setString(1, "001");
pstmt.setBinaryStream(2, fis, (int) file.length());
if (1 != pstmt.executeUpdate()) {
System.err
.println("Incorrect value returned during author insert.");
}
pstmt.close();
String selectSQL = "SELECT image FROM imagelist WHERE id = ?";
PreparedStatement pstmt1 = con.prepareStatement(selectSQL);
pstmt1.setString(1, "001");
ResultSet rs = pstmt1.executeQuery();
rs.next();
byte[] data = rs.getBytes("image");
ImageIcon icon = new ImageIcon(data);
if (icon != null) {
showUI(icon);
}
pstmt1.close();
con.commit();
con.close();
}
public byte[] getBytes(Object obj) throws java.io.IOException {
ByteArrayOutputStream bos = new ByteArrayOutputStream();
ObjectOutputStream oos = new ObjectOutputStream(bos);
oos.writeObject(obj);
oos.flush();
oos.close();
bos.close();
byte[] data = bos.toByteArray();
return data;
}
private Object getObject(byte[] data) throws Exception {
ByteArrayInputStream bos = new ByteArrayInputStream(data);
ObjectInputStream oos = new ObjectInputStream(bos);
return oos.readObject();
}
public void testSaveAndGetImageByObject() throws Exception {
Connection con = getConnection();
String insertSQL = "Insert INTO imagelist VALUES(?, ?)";
PreparedStatement pstmt = con.prepareStatement(insertSQL);
ImageIcon icon = new ImageIcon("boy.gif");
pstmt.setString(1, "002");
pstmt.setBytes(2, getBytes(icon));
if (1 != pstmt.executeUpdate()) {
System.err
.println("Incorrect value returned during author insert.");
}
String selectSQL = "SELECT image FROM imagelist WHERE id = ?";
pstmt = con.prepareStatement(selectSQL);
pstmt.setString(1, "002");
ResultSet rs = pstmt.executeQuery();
rs.next();
byte[] data = rs.getBytes("image");
Object o = getObject(data);
System.out.println(o instanceof ImageIcon);
icon = (ImageIcon) o;
if (icon != null) {
showUI(icon);
}
pstmt.close();
con.close();
}
private void showUI(ImageIcon icon) {
JFrame frame = new JFrame("Show DBImage");
JLabel label = new JLabel(icon);
frame.add(label);
frame.setSize(200, 200);
frame.setVisible(true);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
}
public static void main(String[] args) throws Exception {
StudentDAO myDbTest = new StudentDAO();
myDbTest.getConnection();
}
}