java数据库操作

1.提取单条记录
/*
import java.sql.*;
private Connection conn=null;
private Statement stmt=null;
private ResultSet rs=null;
*/
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url="jdbc:odbc:%%1";
conn=DriverManager.getConnection(url,%%2,%%3);
stmt=conn.createStatement();
rs=stmt.executeQuery(%%4);
rs.next();
int %%5=rs.getInt(%%6);
//String %%5=rs.getString(%%6);
}catch(Exception e){
e.printStackTrace();
}
finally{
try {
if(rs!=null)
rs.close();
if(stmt!=null)
stmt.close();
if(conn!=null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}


2.单值比较
/*
import java.sql.*;
private Connection conn=null;
private Statement stmt=null;
private ResultSet rs=null;
*/
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url="jdbc:odbc:%%1";
conn=DriverManager.getConnection(url,%%2,%%3);
stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
rs=stmt.executeQuery(%%2);
rs.next();
int %%6=rs.getInt(%%7);
if(%%6==%%4)
{
%%5
}
}
catch (Exception e) {
e.printStackTrace();
}
finally{
try {
if(rs!=null)
rs.close();
if(stmt!=null)
stmt.close();
if(conn!=null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}


3.显示表格
/*
import java.awt.*;
import javax.swing.*;
import java.sql.*;
import javax.swing.table.*;
private Connection conn=null;
private Statement stmt=null;
private ResultSet rs=null;
*/
String[] colHeads=new String[]{"au_fname","Phone","City"};
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url="jdbc:odbc:%%1";
conn=DriverManager.getConnection(url,%%2,%%3);
stmt=conn.createStatement();
rs=stmt.executeQuery("Select Count(*) As au_count From %%5");
rs.next();
int iCount=rs.getInt("au_count");
Object[][] data=new Object[iCount][];
int i=0;
rs=stmt.executeQuery("Select * From %%5");
while(rs.next()){
data[i]=new Object[iCount];
data[i][0]=rs.getString("au_fname");
data[i][1]=rs.getString("Phone");
data[i][2]=rs.getString("City");
i++;
}
JTable table=new JTable(data,colHeads);
JScrollPane jsp=new JScrollPane(table);
this.add(jsp);
}catch(Exception e){
e.printStackTrace();
}
finally{
try {
if(rs!=null)
rs.close();
if(stmt!=null)
stmt.close();
if(conn!=null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}


4.操作表格
/*
import java.awt.*;
import javax.swing.*;
import java.sql.*;
import javax.swing.table.*;
private Connection conn = null;
private Statement stmt = null;
private ResultSet rs = null;
*/
JTable table;
Object[][] data;
JButton jb;
JScrollPane jsp;
JComboBox jcb;
JCheckBox jchb;
jcb=new JComboBox();
jchb=new JCheckBox();
jcb.addItem("男");
jcb.addItem("女");
String[] colHeads = { "id", "性别","是否" };
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:%%1";
conn = DriverManager.getConnection(url);
stmt = conn.createStatement();
rs = stmt.executeQuery("Select Count(id) As count From %%2");
rs.next();
int iCount = rs.getInt("count");
data = new Object[iCount][];
int i = 0;
rs = stmt.executeQuery("Select * From %%2");
while (rs.next()) {
data[i] = new Object[iCount];
data[i][0] = rs.getString("id");
data[i][1] = rs.getString("gender");
data[i][2] = rs.getString("isMember");
i++;
}
table = new JTable();
DefaultTableModel dt = new DefaultTableModel(data, colHeads); //
table.setModel(dt);
jsp = new JScrollPane(table);
this.add(jsp, BorderLayout.CENTER);
TableColumn tc = table.getColumn("性别");
TableColumn tc2=table.getColumn("是否");
tc.setCellEditor(new DefaultCellEditor(jcb));
tc2.setCellEditor(new DefaultCellEditor(jchb));
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
if(rs!=null)
rs.close();
if(stmt!=null)
stmt.close();
if(conn!=null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
JPanel jp2 = new JPanel();
this.add(jp2, BorderLayout.NORTH);
JPanel jp = new JPanel(new FlowLayout());
jp2.add(jp);
jp.add(new JTextField(10));


5.数值范围查询
/*
import java.sql.*;
import javax.swing.*;
import java.awt.*;
import javax.swing.table.*;
private Connection conn = null;
private PreparedStatement pst = null;
private ResultSet rs = null;
*/
String[] colHeads=new String[]{"id","name","price"};
JScrollPane jsp;
JTable table;
Object[][] data;
int min = Integer.parseInt(jcb1.getSelectedItem().toString());
int max = Integer.parseInt(jcb2.getSelectedItem().toString());
String sql = "Select Count(*) As pro_count From %%2 Where %%3 Between ? And ?";
try {
conn = DriverManager.getConnection(url);
String url="jdbc:odbc:%%1";
pst = conn.prepareStatement(sql);
pst.setInt(1,min);
pst.setInt(2,max);
rs = pst.executeQuery();
rs.next();
int iCount = rs.getInt("pro_count");
Object[][] data = new Object[iCount][];
pst=conn.prepareStatment("Select * From %%2 Where %%3 Between ? And ?");
int i = 0;
while (rs.next()) {
data[i] = new Object[3];
data[i][0] = rs.getString("id");
data[i][1] = rs.getString("name");
data[i][2] = rs.getString("price");
i++;
}
table=new JTable();
DefaultTableModel dt = new DefaultTableModel(data, colHeads);
table.setModel(dt);
jsp=new JScrollPane(table);
this.add(jsp,BorderLayout.CENTER);
} catch (Exception e1) {
e1.printStackTrace();
} finally{
try {
if(rs!=null)
rs.close();
if(pst!=null)
pst.close();
if(conn!=null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}


6.关闭时关闭连接
//import java.sql.*;
addWindowListener(new WindowAdapter{
public void windowClosing(WindowEvent wevent){
if(stmt!=null){
try {
if(rs!=null)
rs.close();
if(stmt!=null)
stmt.close();
if(conn!=null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}


7.执行命令
/*
import java.sql.*;
private Connection conn=null;
private PreparedStatement pst=null;
*/
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url="jdbc:odbc:%%1";
conn=DriverManager.getConnection(url);
pst=conn.prepareStatement("Insert Into grade(%%2) Values (?)");
pst.setInt(1,%%3);
//pst.setString(2,%%3);
pst.addBatch();
pst.executeBatch();
//pst.execute();
} catch (SQLException e){
e.printStackTrace();
}
finally{
try {
if (pst != null)
pst.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}


8.Oracle8/8i/9i数据库（thin模式）
/*
import java.sql.*;
private Connection conn = null;
private Statement stmt = null;
*/
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); 
String url="jdbc:oracle:thin:@localhost:1521:%%1"; //%%1为数据库的SID
conn= DriverManager.getConnection(url,%%2,%%3);  
stmt=conn.createStatement();


9.DB2数据库
/*
import java.sql.*;
private Connection conn = null;
private Statement stmt = null;
*/
Class.forName("com.ibm.db2.jdbc.app.DB2Driver ").newInstance(); 
String url="jdbc:db2://localhost:5000/%%1"; //%%1为你的数据库名
conn= DriverManager.getConnection(url,%%2,%%3);  
stmt=conn.createStatement();


10.Sql Server7.0/2000数据库
/*
import java.sql.*;
private Connection conn = null;
private Statement stmt = null;
*/
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance(); 
//String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=%%1"; //7.0、2000
String url="jdbc:sqlserver://localhost:1433;DatabaseName=%%1"; //2005
//%%1为数据库名
conn= DriverManager.getConnection(url,%%2,%%3);  
stmt=conn.createStatement();


11.Sybase数据库
/*
import java.sql.*;
private Connection conn = null;
private Statement stmt = null;
*/
Class.forName("com.sybase.jdbc.SybDriver").newInstance(); 
String url =" jdbc:sybase:Tds:localhost:5007/%%1";//%%1为你的数据库名 
Properties sysProps = System.getProperties(); 
sysProps.put("user",%%2); 
sysProps.put("password",%%3); 
conn= DriverManager.getConnection(url, SysProps);  
stmt=conn.createStatement();


12.Informix数据库
/*
import java.sql.*;
private Connection conn = null;
private Statement stmt = null;
*/
Class.forName("com.informix.jdbc.IfxDriver").newInstance(); 
String url = "jdbc:informix-sqli://123.45.67.89:1533/%%1:INFORMIXSERVER=myserver; //%%1为数据库名
conn= DriverManager.getConnection(url,%%2,%%3);  
stmt=conn.createStatement();


13.MySQL数据库
/*
import java.sql.*;
private Connection conn = null;
private Statement stmt = null;
*/
//Class.forName("org.gjt.mm.mysql.Driver").newInstance();
Class.forName("com.mysql.jdbc.Driver");
String url ="jdbc:mysql://localhost:3306/%%1"; //%%1为数据库名
//String url ="jdbc:mysql://localhost:3306/%%1?user=root&password=root&useUnicode=true&characterEncoding=gbk";
//String url ="jdbc:mysql://localhost:3306/%%1?characterEncoding=UTF-8";
//String url ="jdbc:mysql://localhost:3306/%%1?characterEncoding=gbk";
conn= DriverManager.getConnection(url,%%2,%%3); //,"root","root"
stmt=conn.createStatement();


14.PostgreSQL数据库
/*
import java.sql.*;
private Connection conn = null;
private Statement stmt = null;
*/
Class.forName("org.postgresql.Driver").newInstance();
String url ="jdbc:postgresql://localhost/%%1"; //%%1为数据库名
conn= DriverManager.getConnection(url,%%2,%%3);
stmt=conn.createStatement();


15.access数据库直连用ODBC的
/*
import java.sql.*;
private Connection conn = null;
private Statement stmt = null;
*/
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") ; 
String url="jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ="+application.getRealPath(%%1); 
conn = DriverManager.getConnection(url,%%2,%%3); 
stmt=conn.createStatement();


16.程序计时
long time1=System.currentTimeMillis();
long time2=System.currentTimeMillis();
long %%1=time2-time1; //interval


17.延时
try {
Thread.sleep(Integer.Parse(%%1));
} catch(InterruptedException e) {
e.printStackTrace();
}


18.连接Excel文件
/*
import java.sql.*;
private Connection conn = null;
private Statement stmt = null;
*/
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:driver={Microsoft Excel Driver (*.xls)};DBQ="+application.getRealPath(%%1); // 不设置数据源
conn= DriverManager.getConnection(url,%%2,%%3);
stmt=conn.createStatement();


19.GDI+时钟
import java.awt.*;
import java.awt.geom.*;
import java.util.*;
import javax.swing.*;
public class Clock extends JComponent {
private static final Color INTEGRAL_COLOR = new Color(0, 128, 128);
private int radius;
private Calendar currentTime = Calendar.getInstance();
private double s = 0.03;
public Clock(int radius) {
this.radius = radius;
}
public void setCurrentTime(Date time)
{
this.currentTime.setTime(time);
}
public void setCurrentTime(long millis) {
this.currentTime.setTimeInMillis(millis);
}
public Dimension getPreferredSize() {
Insets insets = getInsets();
int r = (int) (radius == -1 ? 0 : radius * (1 + s)) + 1;
return new Dimension(r * 2 + insets.left + insets.right, r * 2
+ insets.top + insets.bottom);
}
protected void paintComponent(Graphics g) {
super.paintComponent(g);
Graphics2D g2d = (Graphics2D) g;
g2d.setRenderingHint(RenderingHints.KEY_ANTIALIASING,
RenderingHints.VALUE_ANTIALIAS_ON);
Insets insets = getInsets();
int wid = getWidth() - insets.left - insets.right;
int hei = getHeight() - insets.top - insets.bottom;
int r = (int) ((Math.min(wid, hei)) / 2 / (1 + s));
g2d.translate(insets.left + r * (1 + s), insets.top + r * (1 + s));
g2d.scale(1, -1);
for (int i = 0; i < 60; i++) {
int angle = 90 - i * 6;
double pos[] = calcPos(r, angle);
paintMinuteDot(r, g2d, pos[0], pos[1], i % 5 == 0);
}
paintHourPointer(r, g2d);
paintMinutePointer(r, g2d);
paintSecondPointer(r, g2d);
paintCenterPoint(g2d);
g2d.scale(1, -1);
g2d.translate(-insets.left - r * (1 + s), -insets.top - r * (1 + s));
}
private void paintCenterPoint(Graphics2D g2d) {
g2d.setColor(Color.BLUE);
Rectangle2D rect = new Rectangle2D.Double(-2, -2, 4, 4);
g2d.fill(rect);
}
private void paintMinutePointer(int r, Graphics2D g2d) {
int minute = currentTime.get(Calendar.MINUTE);
int second = currentTime.get(Calendar.SECOND);
double angle = 90 - (minute + second / 60.0) * 6;
Shape pointerShape = createPointerShape(r * 0.8, r * 0.04, r * 0.08,
angle);
g2d.setColor(Color.LIGHT_GRAY);
g2d.fill(pointerShape);
g2d.setColor(Color.DARK_GRAY);
g2d.draw(pointerShape);
}
private void paintHourPointer(int r, Graphics2D g2d) {
int hour = currentTime.get(Calendar.HOUR);
int minute = currentTime.get(Calendar.MINUTE);
int second = currentTime.get(Calendar.SECOND);
double angle = 90 - (hour + minute / 60.0 + second / 3600.0) * 30;
Shape pointerShape = createPointerShape(r * 0.6, r * 0.06, r * 0.1,
angle);
g2d.setColor(Color.LIGHT_GRAY);
g2d.fill(pointerShape);
g2d.setColor(Color.DARK_GRAY);
g2d.draw(pointerShape);
}
private Shape createPointerShape(double r1, double r2, double r3,
double angle) {
GeneralPath gp = new GeneralPath();
double[] pos = calcPos(r1, angle);
double[] pos1 = calcPos(r2, angle + 90);
gp.append(new Line2D.Double(pos[0], pos[1], pos1[0], pos1[1]), true);
double[] pos2 = calcPos(r3, angle + 180);
gp.lineTo((float) pos2[0], (float) pos2[1]);
double[] pos3 = calcPos(r2, angle + 270);
gp.lineTo((float) pos3[0], (float) pos3[1]);
gp.closePath();
return gp;
}
private void paintSecondPointer(int r, Graphics2D g2d) {
g2d.setColor(Color.BLACK);
int second = currentTime.get(Calendar.SECOND);
int angle = 90 - second * 6;
double pos[] = calcPos(r * 0.9, angle);
double pos1[] = calcPos(r * 0.2, angle + 180);
Line2D line = new Line2D.Double(pos1[0], pos1[1], pos[0], pos[1]);
g2d.draw(line);
}
private void paintMinuteDot(int r, Graphics2D g2d, double x, double y,
boolean flag) {
g2d.setColor(flag ? Color.RED : Color.BLACK);
if (flag) {
// Rectangle2D rect = new Rectangle2D.Double(
Ellipse2D rect = new Ellipse2D.Double(x - r * s, y - r * s, r * s
* 2, r * s * 2);
g2d.fill(rect);
} else {
// Rectangle2D rect = new Rectangle2D.Double(
Ellipse2D rect = new Ellipse2D.Double(x - r * 0.02, y - r * 0.02,
r * 0.04, r * 0.04);
g2d.fill(rect);
}
}
private double[] calcPos(double r, double angle) {
double radian = Math.toRadians(angle);
double x = r * Math.cos(radian);
double y = r * Math.sin(radian);


return new double[] { x, y };
}
public static void main(String[] args) {
try {
UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName());
} catch (Exception e) {
e.printStackTrace();
}
final Clock clock = new Clock(50);
clock.setBorder(BorderFactory.createEmptyBorder(10, 10, 10, 10));
JFrame f = new JFrame("GDI+时钟");
// f.setBounds(380,200,500,600);
f.this.add(clock, BorderLayout.CENTER);
f.pack();
f.setLocationRelativeTo(null);
f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
f.setVisible(true);
new Thread() {
public void run() {
while (true) {
try {
Thread.sleep(1000);
} catch (InterruptedException ex) {
ex.printStackTrace();
}
clock.setCurrentTime(System.currentTimeMillis());
clock.repaint();
}
}
}.start();
}
}


20.数据库存入二进制字段数据
/*
import java.sql.*;
private Connection conn = null;
private PreparedStatement stmt = null;
*/
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:%%1");
conn= DriverManager.getConnection(url,%%2,%%3);
InputStream pic = new FileInputStream(dto.get(i).getLibPic());
sql = "INSERT INTO piclib (name,pic,sign,remark) VALUES (?,?,?,?)";
pst = conn.prepareStatement(sql);
pst.setString(1, dto.get(i).getName());           
pst.setBinaryStream(2,pic,(int)dto.get(i).getLibPic().length());


21.数据库取出二进制字段数据
/*
import java.sql.*;
private Connection conn = null;
private ResultSet rs = null;
private PreparedStatement pstmt = null;
*/
public class DemoDisplayBinaryDataFromDatabase {
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "name";
String password = "password";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
}


public static class BitmapHelper   
{   
public static Bitmap BytesToBitmap(byte[] Bytes)   
{   
MemoryStream stream = null;   
try  
{   
stream = new MemoryStream(Bytes);   
return new Bitmap((Image)new Bitmap(stream));   
}   
catch (ArgumentNullException ex)   
{   
throw ex;   
}   
catch (ArgumentException ex)   
{   
throw ex;   
}   
finally  
{   
stream.Close();   
}   
}   


public static byte[] BitmapToBytes(Bitmap Bitmap)   
{   
MemoryStream ms = null;   
try  
{   
ms = new MemoryStream();   
Bitmap.Save(ms, Bitmap.RawFormat);   
byte[] byteImage = new Byte[ms.Length];   
byteImage = ms.ToArray();   
return byteImage;   
}   
catch (ArgumentNullException ex)   
{   
throw ex;   
}   
finally  
{   
ms.Close();   
}   
}   
}  


public static class BitmapHelper
{
public static Bitmap BytesToBitmap(byte[] Bytes)
{
MemoryStream stream = null;
try
{
stream = new MemoryStream(Bytes);
return new Bitmap((Image)new Bitmap(stream));
}
catch (ArgumentNullException ex)
{
throw ex;
}
catch (ArgumentException ex)
{
throw ex;
}
finally
{
stream.Close();
}
}
public static byte[] BitmapToBytes(Bitmap Bitmap)
{
MemoryStream ms = null;
try
{
ms = new MemoryStream();
Bitmap.Save(ms, Bitmap.RawFormat);
byte[] byteImage = new Byte[ms.Length];
byteImage = ms.ToArray();
return byteImage;
}
catch (ArgumentNullException ex)
{
throw ex;
}
finally
{
ms.Close();
}
}
}
/*
存进数据库可以直接用Image对象： 
SqlCommand.Parameters.Add("@image",SqlDbType.Image).Value=myImage;(myImage为Image对象) 
从数据库取出来，是byte数组，可以用上面的函数进行转换
*/
String query = "SELECT raw_column, long_raw_column FROM binary_table WHERE id = ?";
try {
conn = getConnection();
Object[] results = new Object[2];
pstmt = conn.prepareStatement(query);
pstmt.setString(1, "0001");
rs = pstmt.executeQuery();
rs.next();
// materialize binary data onto client
results[0] = rs.getBytes("RAW_COLUMN");
results[1] = rs.getBytes("LONG_RAW_COLUMN");
} finally {
rs.close();
pstmt.close();
conn.close();
}


22.批量执行SQL和存储过程
/*
import java.sql.*;
private Connection conn = null;
private PreparedStatement pst = null;
private CallableStatement cs = null;
*/
try {
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
String url = "jdbc:oracle:thin:@localhost:1521:%%1"; // %%1为数据库的SID
conn = DriverManager.getConnection(url,%%2,%%3);
cs = conn.prepareCall("{call abc(?,?}");
pst = conn.prepareStatement("Insert Into grade(%%1) Values (?)");
pst.setString(1, "");
// pst.setInt(2,%%2);
pst.addBatch();
pst.executeBatch();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (pst != null)
pst.close();
if (cs != null)
cs.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}


23.自动填入数据
/*
import java.awt.*;
import java.awt.event.*;
import java.awt.datatransfer.*;
*/
Clipboard clipboard = Toolkit.getDefaultToolkit().getSystemClipboard();
Transferable tText = new StringSelection("abcD");
clipboard.setContents(tText, null);
try {
Robot r = new Robot();
r.keyPress(KeyEvent.VK_CONTROL);
r.keyPress(KeyEvent.VK_V);
r.keyRelease(KeyEvent.VK_V);
r.keyRelease(KeyEvent.VK_CONTROL);
} catch (AWTException e) {
e.printStackTrace();
}


24.验证码随机数
//import java.awt.*;
Random random = new Random(555L);
int %%1=random.nextInt(8999)+1000;


25.查数据库表的Id最大值
//import java.sql.*;
private int getTableId(Connection conn, String table) {
int returnId = 0;// 默认为无记录
String sqlString = "Select * From ?";
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sqlString);
ps.setString(1, table);
rs = ps.executeQuery();
rs.afterLast();
returnId = rs.getRow();// 让returnId等于表中记录总数
} catch (SQLException e) {
returnId = -1; // 捕获到异常则代表数据库中没有此表
} finally {
if (returnId > 0) {
try {
ps = conn.prepareStatement("Select Max(id) From ?");
ps.setString(1, table);
ps.addBatch();
rs=ps.executeQuery();
rs.next();
returnId=rs.getInt("id");
} catch (SQLException e) {
e.printStackTrace();
}
}
try {
rs.close();
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return returnId;
}


26.mysql分页
/*
import java.sql.*;
import java.util.*;
private Connection conn=null;
private PreparedStatement pst=null;
private ResultSet rs=null;
private ResultSetMetaData rsmd=null;
private int rowCount=0;
private int pageCount=0;
private int pageSize=0;
*/
String sqlStr=%%1;
pageSize=%%2; //2000
int ipage=%%3; //1
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e4) {
e4.printStackTrace();
}
try{
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/%%1",%%2,%%3);
//conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/%%1?characterEncoding=UTF-8",%%2,%%3);
//conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/%%1?characterEncoding=gbk",%%2,%%3);
String sqlStr = "Select * From ? Where id>? limit ?,?";
pst= con.prepareStatement(sqlStr);
int irows=pageSize*(ipage-1);
pst.setString(1, table1);
pst.setInt(2, targetid);
if (rs != null) {
rs.last();
rowCount = rs.getRow();
rs.first();
pageCount = (rowCount - 1) / pageSize + 1;
}
pst.setInt(3, irows);
pst.setInt(4, pageSize);
rs=pst.executeQuery();
//rsmd = rs.getMetaData();
if(rs!=null)
{
rs.last();
rowCount=rs.getRow();
rs.first();
pageCount=(rowCount-1)/pageSize+1;
}
rs=pst.executeQuery(strSql);
rsmd=rs.getMetaData();
}
catch(SQLException e)
{
e.printStackTrace();
}
Vector vData=new Vector();
try{
if(rs!=null)
{
while(rs.next())
{
String[] sData=new String[rsmd.getColumnCount()];
for(int j=0;j<rsmd.getColumnCount();j++)
{
sData[j]=rs.getString(j+1);
}
vData.addElement(sData);
}
rs.close();
}
if(pst!=null)
pst.close();
if(conn!=null)
conn.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
Vector %%4=vData;


27.Sql Server 2005分页
/*
import java.sql.*;
import java.util.*;
private Connection conn=null;
private PreparedStatement pst=null;
private ResultSet rs=null;
private ResultSetMetaData rsmd=null;
private int rowCount=0;
private int pageCount=0;
private int pageSize=0;
*/
String sqlStr=%%1;
pageSize=%%2; //2000
int ipage=%%3; //1
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
} catch (ClassNotFoundException e4) {
e4.printStackTrace();
}
//String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=%%1"; //7.0、2000
String url="jdbc:sqlserver://localhost:1433;DatabaseName=%%1"; //2005
try{
conn= DriverManager.getConnection(url,%%2,%%3);


SELECT TOP %%2 * FROM %%1 WHERE (ID NOT IN (SELECT TOP %%2*%%3 id FROM %%1 ORDER BY id)) ORDER BY ID


页大小
%%2


页数
%%3


String sqlStr = "Select top 1 * From ? Where id>? limit ?,?"; //"Select top 1 * From ? Where id>? limit ?,?"
pst= con.prepareStatement(sqlStr);
int irows=pageSize*(ipage-1);
pst.setString(1, table1);
pst.setInt(2, targetid);
if (rs != null) {
rs.last();
rowCount = rs.getRow();
rs.first();
pageCount = (rowCount - 1) / pageSize + 1;
}
pst.setInt(3, irows);
pst.setInt(4, pageSize);
rs=pst.executeQuery();
//rsmd = rs.getMetaData();
if(rs!=null)
{
rs.last();
rowCount=rs.getRow();
rs.first();
pageCount=(rowCount-1)/pageSize+1;
}
sqlStr+=(" limit "+irows+","+pageSize);
pst=con.createStatement();
rs=pst.executeQuery(strSql);
rsmd=rs.getMetaData();
}
catch(SQLException e)
{
e.printStackTrace();
}
Vector vData=new Vector();
try{
if(rs!=null)
{
while(rs.next())
{
String[] sData=new String[rsmd.getColumnCount()];
for(int j=0;j<rsmd.getColumnCount();j++)
{
sData[j]=rs.getString(j+1);
}
vData.addElement(sData);
}
rs.close();
}
if(pst!=null)
pst.close();
if(conn!=null)
conn.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
Vector %%4=vData;


28.Oracle分页
/*
import java.sql.*;
import java.util.*;
private Connection conn=null;
private PreparedStatement pst=null;
private ResultSet rs=null;
private ResultSetMetaData rsmd=null;
private int rowCount=0;
private int pageCount=0;
private int pageSize=0;
*/
String sqlStr=%%1;
pageSize=%%2; //2000
int ipage=%%3; //1
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:%%1";
try {
Class.forName(driver);
} catch (ClassNotFoundException e4) {
e4.printStackTrace();
}
try{
conn= DriverManager.getConnection(url,%%4,%%5);
String sqlStr = "Select * From ? Where id>? limit ?,?"; //parition
pst= con.prepareStatement(sqlStr);
int irows=pageSize*(ipage-1);
pst.setString(1, table1);
pst.setInt(2, targetid);
if (rs != null) {
rs.last();
rowCount = rs.getRow();
rs.first();
pageCount = (rowCount - 1) / pageSize + 1;
}
pst.setInt(3, irows);
pst.setInt(4, pageSize);
rs=pst.executeQuery();
//rsmd = rs.getMetaData();
if(rs!=null)
{
rs.last();
rowCount=rs.getRow();
rs.first();
pageCount=(rowCount-1)/pageSize+1;
}
sqlStr+=(" limit "+irows+","+pageSize);
pst=con.createStatement();
rs=pst.executeQuery(strSql);
rsmd=rs.getMetaData();
}
catch(SQLException e)
{
e.printStackTrace();
}
Vector vData=new Vector();
try{
if(rs!=null)
{
while(rs.next())
{
String[] sData=new String[rsmd.getColumnCount()];
for(int j=0;j<rsmd.getColumnCount();j++)
{
sData[j]=rs.getString(j+1);
}
vData.addElement(sData);
}
rs.close();
}
if(pst!=null)
pst.close();
if(conn!=null)
conn.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
Vector %%4=vData;


29.生成GUID
//两种方式生成guid 与uuid 
//需要comm log 库
/*
import java.net.*;
import java.security.*;
import java.util.*;
   protected final org.apache.commons.logging.Log logger = org.apache.commons.logging.LogFactory
      .getLog(getClass());
   public String valueBeforeMD5 = "";
   public String valueAfterMD5 = "";
   private static Random myRand;
   private static SecureRandom mySecureRand;
   private static String s_id;
   private static final int PAD_BELOW = 0x10;
   private static final int TWO_BYTES = 0xFF;
    // Static block to take care of one time secureRandom seed.
    // It takes a few seconds to initialize SecureRandom.  You might
    // want to consider removing this static block or replacing
    // it with a "time since first loaded" seed to reduce this time.
    // This block will run only once per JVM instance.
   static {
      mySecureRand = new SecureRandom();
      long secureInitializer = mySecureRand.nextLong();
      myRand = new Random(secureInitializer);
      try {
         s_id = InetAddress.getLocalHost().toString();
      } catch (UnknownHostException e) {
         e.printStackTrace();
      }
   }
*/


   /*
    * Default constructor.  With no specification of security option,
    * this constructor defaults to lower security, high performance.
    */
   public RandomGUID() {
      getRandomGUID(false);
   }
   /*
    * Constructor with security option.  Setting secure true
    * enables each random number generated to be cryptographically
    * strong.  Secure false defaults to the standard Random function seeded
    * with a single cryptographically strong random number.
    */
   public RandomGUID(boolean secure) {
      getRandomGUID(secure);
   }
   /*
    * Method to generate the random GUID
    */
   private void getRandomGUID(boolean secure) {
      MessageDigest md5 = null;
      StringBuffer sbValueBeforeMD5 = new StringBuffer(128);
      try {
         md5 = MessageDigest.getInstance("MD5");
      } catch (NoSuchAlgorithmException e) {
         logger.error("Error: " + e);
      }
      try {
         long time = System.currentTimeMillis();
         long rand = 0;
         if (secure) {
            rand = mySecureRand.nextLong();
         } else {
            rand = myRand.nextLong();
         }
         sbValueBeforeMD5.append(s_id);
         sbValueBeforeMD5.append(":");
         sbValueBeforeMD5.append(Long.toString(time));
         sbValueBeforeMD5.append(":");
         sbValueBeforeMD5.append(Long.toString(rand));
         valueBeforeMD5 = sbValueBeforeMD5.toString();
         md5.update(valueBeforeMD5.getBytes());
         byte[] array = md5.digest();
         StringBuffer sb = new StringBuffer(32);
         for (int j = 0; j < array.length; ++j) {
            int b = array[j] & TWO_BYTES;
            if (b < PAD_BELOW)
               sb.append('0');
            sb.append(Integer.toHexString(b));
         }
         valueAfterMD5 = sb.toString();
      } catch (Exception e) {
         logger.error("Error:" + e);
      }
   }
   /*
    * Convert to the standard format for GUID
    * (Useful for SQL Server UniqueIdentifiers, etc.)
    * Example: C2FEEEAC-CFCD-11D1-8B05-00600806D9B6
    */
   public String toString() {
      String raw = valueAfterMD5.toUpperCase();
      StringBuffer sb = new StringBuffer(64);
      sb.append(raw.substring(0, 8));
      sb.append("-");
      sb.append(raw.substring(8, 12));
      sb.append("-");
      sb.append(raw.substring(12, 16));
      sb.append("-");
      sb.append(raw.substring(16, 20));
      sb.append("-");
      sb.append(raw.substring(20));
      return sb.toString();
   }
         //valueBeforeMD5 //Seeding String
         //valueAfterMD5 //rawGUID
         //toString() //RandomGUID
//UUID uuid = UUID.randomUUID();


30.计算日期时间差
/*
import java.text.*;
import java.util.*;
import java.awt.*;
import java.awt.datatransfer.*;
import java.io.*;
*/
public static long fromDateStringToLong(String inVal) { // 此方法计算时间毫秒
Date date = null; // 定义时间类型
SimpleDateFormat inputFormat = new SimpleDateFormat("MM-dd-yy hh:mm");
try {
date = inputFormat.parse(inVal); // 将字符型转换成日期型
} catch (Exception e) {
e.printStackTrace();
}
return date.getTime(); // 返回毫秒数
}
// 取得系统剪贴板里可传输的数据构造的Java对象
Transferable t = Toolkit.getDefaultToolkit().getSystemClipboard()
.getContents(null);
try {
if (t != null && t.isDataFlavorSupported(DataFlavor.stringFlavor)) {
// 因为原系的剪贴板里有多种信息, 如文字, 图片, 文件等
// 先判断开始取得的可传输的数据是不是文字, 如果是, 取得这些文字
String s = (String) t.getTransferData(DataFlavor.stringFlavor);
String[] arr = s.split("\n");
StringBuilder sb = new StringBuilder(1024);
for (String sss : arr) {
if (!sss.trim().equals("")) {
long startT = fromDateStringToLong(sss); // 定义上机时间
long endT = new Date().getTime(); // 定义下机时间
long ss = (endT - startT) / (1000); // 共计秒数
int MM = (int) ss / 60; // 共计分钟数
int hh = (int) ss / 3600; // 共计小时数
int dd = (int) hh / 24; // 共计天数
hh = hh - dd * 24;
MM = MM - (dd * 24 + hh) * 60;
sb.append((dd >= 100 ? "" : "0"));
sb.append((dd >= 10 ? "" : "0"));
sb.append(dd);
sb.append(" ");
sb.append(hh);
sb.append(":");
sb.append(MM);
sb.append("\r\n");
}
}
Clipboard clipboard = Toolkit.getDefaultToolkit()
.getSystemClipboard();
Transferable tText = new StringSelection(sb.toString());
clipboard.setContents(tText, null);
}
} catch (UnsupportedFlavorException ex) {
ex.printStackTrace();
} catch (IOException ex) {
ex.printStackTrace();
}