Java กับคำสั่ง SQL Query ใช้ร่วมกับ MariaDB 10.0.x กับ PostgreSQL 9.5x86 เขียนได้อย่างไร

ในห้อง 'คอมพิวเตอร์ & อินเตอร์เน็ต' ตั้งกระทู้โดย ledphong, 3 พฤศจิกายน 2016.

  1. ledphong

    ledphong เป็นที่รู้จักกันดี

    วันที่สมัครสมาชิก:
    28 มีนาคม 2009
    โพสต์:
    1,425
    ค่าพลัง:
    +165
    มาดูตัวอย่างครับ
     
  2. ledphong

    ledphong เป็นที่รู้จักกันดี

    วันที่สมัครสมาชิก:
    28 มีนาคม 2009
    โพสต์:
    1,425
    ค่าพลัง:
    +165
    Code ::Happy BirthDay(MariaDB)
    =====================
    <pre style="background:#000;color:#f8f8f8"><span style="color:#e28964">private</span> <span style="color:#dad085">void</span> showdata() {
    <span style="color:#dad085">try</span>{
    Class.forName(<span style="color:#65b042">"org.mariadb.jdbc.Driver"</span>);
    <span style="color:#dad085">connect</span> <span style="color:#e28964">=</span> DriverManager.getConnection(<span style="color:#65b042">"jdbc:mariadb://localhost/dbolder"</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"?user=root&amp;password=gomplayer"</span>);

    <span style="color:#99cf50">String</span> sql <span style="color:#e28964">=</span> <span style="color:#65b042">"SELECT tblolder.CodePK,tblolder.SexFK,"</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"tblolder.TitleFK,CONCAT_WS(' ',tblolder.FirstName,tblolder.LastName) As FirstName,"</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"tblolder.IDCard,DATE_FORMAT(DATE_ADD(tblolder.BirthDate, INTERVAL 543 YEAR),'%d/%m/%Y') AS BirthDate,tblolder.BanID ,"</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"tblolder.Moo,tblolder.TypeNameFK,"</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"tblolder.MethodMoneyFK ,DATE_FORMAT(DATE_ADD(tblsetting.CalYMD, INTERVAL 543 YEAR),'%d/%m/%Y') AS CalYMD ,"</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"TRUNCATE((DATEDIFF(tblsetting.CalYMD,tblolder.BirthDate)/365.25),0) as Age ,"</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"case when (TRUNCATE((DATEDIFF(tblsetting.CalYMD,tblolder.BirthDate)/365.25),0)) >= 90 then '1000.00' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE((DATEDIFF(tblsetting.CalYMD,tblolder.BirthDate)/365.25),0)) >= 80 then '800.00' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE((DATEDIFF(tblsetting.CalYMD,tblolder.BirthDate)/365.25),0)) >= 70 then '700.00' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE((DATEDIFF(tblsetting.CalYMD,tblolder.BirthDate)/365.25),0)) >= 60 then '600.00' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"else '0.00' END as Money "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"FROM tblolder Left Join tblsetting "</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"ON tblolder.Interaction = tblsetting.Interaction "</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"WHERE "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"DAYOFMONTH(tblolder.BirthDate) = DAYOFMONTH(CURDATE()) "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"AND MONTH(tblolder.BirthDate) = MONTH(CURDATE()) "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"AND tblolder.MethodMoneyFK like '%"</span> <span style="color:#e28964">+</span> txtSearchMethodMoney.<span style="color:#dad085">getText</span>() <span style="color:#e28964">+</span> <span style="color:#65b042">"%' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"AND tblolder.Moo like '%"</span> <span style="color:#e28964">+</span> txtSearchMoo.<span style="color:#dad085">getText</span>() <span style="color:#e28964">+</span> <span style="color:#65b042">"%' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"AND tblolder.FirstName like '%"</span> <span style="color:#e28964">+</span> txtSearchFirstName.<span style="color:#dad085">getText</span>() <span style="color:#e28964">+</span> <span style="color:#65b042">"%' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"ORDER BY tblolder.Moo,tblolder.BanID ASC"</span>;
    <span style="color:#aeaeae;font-style:italic">//"ORDER BY tblolder.CodePK ASC";</span>

    pstmt <span style="color:#e28964">=</span> <span style="color:#dad085">connect</span>.prepareStatement(sql);<span style="color:#aeaeae;font-style:italic">//new</span>
    rec <span style="color:#e28964">=</span> pstmt.executeQuery();<span style="color:#aeaeae;font-style:italic">//new</span>

    <span style="color:#dad085">int</span> row <span style="color:#e28964">=</span> <span style="color:#3387cc">0</span>;
    <span style="color:#e28964">while</span>((rec<span style="color:#e28964">!</span><span style="color:#e28964">=</span><span style="color:#3387cc">null</span>) <span style="color:#e28964">&amp;</span><span style="color:#e28964">&amp;</span> (rec.<span style="color:#dad085">next</span>()))
    {
    model.addRow(<span style="color:#e28964">new</span> <span style="color:#9b859d">Object</span>[<span style="color:#3387cc">0</span>]);
    model.setValueAt(rec.getString(<span style="color:#65b042">"CodePK"</span>), row, <span style="color:#3387cc">0</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"SexFK"</span>), row, <span style="color:#3387cc">1</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"TitleFK"</span>), row, <span style="color:#3387cc">2</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"FirstName"</span>), row, <span style="color:#3387cc">3</span>);
    <span style="color:#aeaeae;font-style:italic">//model.setValueAt(rec.getString("LastName"), row, 4);</span>
    model.setValueAt(rec.getString(<span style="color:#65b042">"IDCard"</span>), row, <span style="color:#3387cc">4</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"BirthDate"</span>), row, <span style="color:#3387cc">5</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"BanID"</span>), row, <span style="color:#3387cc">6</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"Moo"</span>), row, <span style="color:#3387cc">7</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"TypeNameFK"</span>), row, <span style="color:#3387cc">8</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"MethodMoneyFK"</span>), row, <span style="color:#3387cc">9</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"CalYMD"</span>), row, <span style="color:#3387cc">10</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"Age"</span>), row, <span style="color:#3387cc">11</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"Money"</span>), row, <span style="color:#3387cc">12</span>);

    <span style="color:#aeaeae;font-style:italic">//model.setValueAt(rec.getFloat("Budget"), row, 6);</span>
    <span style="color:#aeaeae;font-style:italic">//model.setValueAt(rec.getFloat("Used"), row, 7);</span>

    row<span style="color:#e28964">+</span><span style="color:#e28964">+</span>;
    }

    <span style="color:#aeaeae;font-style:italic">// Header Font &amp; Color</span>
    table.getTableHeader().setFont(<span style="color:#e28964">new</span> Font(<span style="color:#65b042">"SansSerif"</span>, Font.BOLD, <span style="color:#3387cc">12</span>));
    table.getTableHeader().setForeground(<span style="color:#9b859d">Color</span>.blue);

    <span style="color:#aeaeae;font-style:italic">// Column Width</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">0</span>).setPreferredWidth(<span style="color:#3387cc">10</span>);<span style="color:#aeaeae;font-style:italic">//CodePK</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">1</span>).setPreferredWidth(<span style="color:#3387cc">10</span>);<span style="color:#aeaeae;font-style:italic">//Sex</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">2</span>).setPreferredWidth(<span style="color:#3387cc">30</span>);<span style="color:#aeaeae;font-style:italic">//TitleFK</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">3</span>).setPreferredWidth(<span style="color:#3387cc">120</span>);<span style="color:#aeaeae;font-style:italic">//FirstName</span>
    <span style="color:#aeaeae;font-style:italic">//table.getColumnModel().getColumn(4).setPreferredWidth(80);//LastName</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">4</span>).setPreferredWidth(<span style="color:#3387cc">70</span>);<span style="color:#aeaeae;font-style:italic">//IDCard</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">5</span>).setPreferredWidth(<span style="color:#3387cc">20</span>);<span style="color:#aeaeae;font-style:italic">//BirthDate</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">6</span>).setPreferredWidth(<span style="color:#3387cc">20</span>);<span style="color:#aeaeae;font-style:italic">//BanID</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">7</span>).setPreferredWidth(<span style="color:#3387cc">10</span>);<span style="color:#aeaeae;font-style:italic">//Moo</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">8</span>).setPreferredWidth(<span style="color:#3387cc">120</span>);<span style="color:#aeaeae;font-style:italic">//TypeNameFK</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">9</span>).setPreferredWidth(<span style="color:#3387cc">40</span>);<span style="color:#aeaeae;font-style:italic">//MethodMoneyFK</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">10</span>).setPreferredWidth(<span style="color:#3387cc">20</span>);<span style="color:#aeaeae;font-style:italic">//CalYMD</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">11</span>).setPreferredWidth(<span style="color:#3387cc">10</span>);<span style="color:#aeaeae;font-style:italic">//Age</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">12</span>).setPreferredWidth(<span style="color:#3387cc">20</span>);<span style="color:#aeaeae;font-style:italic">//Money</span>

    <span style="color:#aeaeae;font-style:italic">// Row Height</span>
    table.<span style="color:#dad085">setRowHeight</span>(<span style="color:#3387cc">20</span>);

    <span style="color:#aeaeae;font-style:italic">//======Count Budget=============</span>
    <span style="color:#99cf50">String</span> com1 <span style="color:#e28964">=</span> <span style="color:#65b042">"SELECT Budget "</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"FROM tblsetting "</span>;

    pstmt <span style="color:#e28964">=</span> <span style="color:#dad085">connect</span>.prepareStatement(com1);<span style="color:#aeaeae;font-style:italic">//new</span>
    rec <span style="color:#e28964">=</span> pstmt.executeQuery();<span style="color:#aeaeae;font-style:italic">//new</span>

    <span style="color:#e28964">if</span> (rec.<span style="color:#dad085">next</span>()) {
    <span style="color:#dad085">int</span> maxID <span style="color:#e28964">=</span> rec.getInt(<span style="color:#3387cc">1</span>);

    txtBudget.setText(<span style="color:#99cf50">String</span>.<span style="color:#dad085">valueOf</span>(maxID));
    }

    <span style="color:#aeaeae;font-style:italic">//=======Calculate================</span>
    Calculate();
    <span style="color:#aeaeae;font-style:italic">//================================</span>
    pstmt.<span style="color:#dad085">close</span>();
    <span style="color:#dad085">connect</span>.<span style="color:#dad085">close</span>();
    }<span style="color:#dad085">catch</span>(ClassNotFoundException | SQLException ex){
    JOptionPane.showMessageDialog(<span style="color:#3387cc">null</span>, <span style="color:#65b042">"ไม่สามารถแสดงข้อมูลได้!<span style="color:#ddf2a4">\n</span>"</span><span style="color:#e28964">+</span>ex.<span style="color:#dad085">getMessage</span>());
    }
    }
    </pre>
     
  3. ledphong

    ledphong เป็นที่รู้จักกันดี

    วันที่สมัครสมาชิก:
    28 มีนาคม 2009
    โพสต์:
    1,425
    ค่าพลัง:
    +165
    Code ::Happy BirthDay(PostgreSQL 9.5x86)
    ===========================
    <pre style="background:#000;color:#f8f8f8">private void showdata() {
    try{
    connect <span style="color:#e28964">=</span> getDBConnection();

    String sql <span style="color:#e28964">=</span> <span style="color:#65b042">"select tblolder.codepk ,tblolder.sexfk,"</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"tblolder.titlefk,concat_ws(' ',tblolder.firstname,tblolder.lastname) as firstname,"</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"tblolder.idcard,to_char(tblolder.birthdate + INTERVAL '543 year' , 'DD/MM/YYYY') as birthdate,tblolder.banid ,"</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"tblolder.moo,tblolder.typenamefk,"</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"tblolder.methodmoneyfk ,to_char(tblsetting.calymd + INTERVAL '543 year' , 'DD/MM/YYYY') as calymd ,"</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"TRUNC((EXTRACT(year FROM age(tblsetting.calymd::date,tblolder.birthdate::date))*12 + EXTRACT(month FROM age(tblsetting.calymd::date,tblolder.birthdate::date)))/12) as age ,"</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"(case when (TRUNC((EXTRACT(year FROM age(tblsetting.calymd::date,tblolder.birthdate::date))*12 + EXTRACT(month FROM age(tblsetting.calymd::date,tblolder.birthdate::date)))/12)) >= 90 then '1000.00' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNC((EXTRACT(year FROM age(tblsetting.calymd::date,tblolder.birthdate::date))*12 + EXTRACT(month FROM age(tblsetting.calymd::date,tblolder.birthdate::date)))/12)) >= 80 then '800.00' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNC((EXTRACT(year FROM age(tblsetting.calymd::date,tblolder.birthdate::date))*12 + EXTRACT(month FROM age(tblsetting.calymd::date,tblolder.birthdate::date)))/12)) >= 70 then '700.00' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNC((EXTRACT(year FROM age(tblsetting.calymd::date,tblolder.birthdate::date))*12 + EXTRACT(month FROM age(tblsetting.calymd::date,tblolder.birthdate::date)))/12)) >= 60 then '600.00' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"else '0.00' end) as money "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"from tblolder left join tblsetting "</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"on tblolder.interaction = tblsetting.interaction "</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"where "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"Extract(day from tblolder.birthdate::date) = Extract(day from NOW()::date) "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"and Extract(month from tblolder.birthdate::date) = Extract(month from NOW()::date) "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"and tblolder.methodmoneyfk like '%"</span> <span style="color:#e28964">+</span> <span style="color:#3387cc">txtSearchMethodMoney</span>.<span style="color:#3387cc">getText</span>() <span style="color:#e28964">+</span> <span style="color:#65b042">"%' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"and tblolder.moo like '%"</span> <span style="color:#e28964">+</span> <span style="color:#3387cc">txtSearchMoo</span>.<span style="color:#3387cc">getText</span>() <span style="color:#e28964">+</span> <span style="color:#65b042">"%' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"and tblolder.firstname like '%"</span> <span style="color:#e28964">+</span> <span style="color:#3387cc">txtSearchFirstName</span>.<span style="color:#3387cc">getText</span>() <span style="color:#e28964">+</span> <span style="color:#65b042">"%' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"order by tblolder.moo,tblolder.banid asc"</span>;

    pstmt <span style="color:#e28964">=</span> <span style="color:#3387cc">connect</span>.<span style="color:#3387cc">prepareStatement</span>(sql);<span style="color:#e28964">/</span><span style="color:#e28964">/</span>new
    rec <span style="color:#e28964">=</span> <span style="color:#3387cc">pstmt</span>.<span style="color:#3387cc">executeQuery</span>();<span style="color:#e28964">/</span><span style="color:#e28964">/</span>new

    <span style="color:#99cf50">int</span> row <span style="color:#e28964">=</span> <span style="color:#3387cc">0</span>;
    while((rec<span style="color:#e28964">!=</span><span style="color:#e28964">null</span>) &amp;&amp; (<span style="color:#3387cc">rec</span>.<span style="color:#3387cc">next</span>()))
    {
    <span style="color:#3387cc">model</span>.<span style="color:#3387cc">addRow</span>(new Object[<span style="color:#3387cc">0</span>]);
    <span style="color:#3387cc">model</span>.<span style="color:#3387cc">setValueAt</span>(<span style="color:#3387cc">rec</span>.<span style="color:#3387cc">getString</span>(<span style="color:#65b042">"codepk"</span>), row, <span style="color:#3387cc">0</span>);
    <span style="color:#3387cc">model</span>.<span style="color:#3387cc">setValueAt</span>(<span style="color:#3387cc">rec</span>.<span style="color:#3387cc">getString</span>(<span style="color:#65b042">"sexfk"</span>), row, <span style="color:#3387cc">1</span>);
    <span style="color:#3387cc">model</span>.<span style="color:#3387cc">setValueAt</span>(<span style="color:#3387cc">rec</span>.<span style="color:#3387cc">getString</span>(<span style="color:#65b042">"titlefk"</span>), row, <span style="color:#3387cc">2</span>);
    <span style="color:#3387cc">model</span>.<span style="color:#3387cc">setValueAt</span>(<span style="color:#3387cc">rec</span>.<span style="color:#3387cc">getString</span>(<span style="color:#65b042">"firstname"</span>), row, <span style="color:#3387cc">3</span>);
    <span style="color:#3387cc">model</span>.<span style="color:#3387cc">setValueAt</span>(<span style="color:#3387cc">rec</span>.<span style="color:#3387cc">getString</span>(<span style="color:#65b042">"idcard"</span>), row, <span style="color:#3387cc">4</span>);
    <span style="color:#3387cc">model</span>.<span style="color:#3387cc">setValueAt</span>(<span style="color:#3387cc">rec</span>.<span style="color:#3387cc">getString</span>(<span style="color:#65b042">"birthdate"</span>), row, <span style="color:#3387cc">5</span>);
    <span style="color:#3387cc">model</span>.<span style="color:#3387cc">setValueAt</span>(<span style="color:#3387cc">rec</span>.<span style="color:#3387cc">getString</span>(<span style="color:#65b042">"banid"</span>), row, <span style="color:#3387cc">6</span>);
    <span style="color:#3387cc">model</span>.<span style="color:#3387cc">setValueAt</span>(<span style="color:#3387cc">rec</span>.<span style="color:#3387cc">getString</span>(<span style="color:#65b042">"moo"</span>), row, <span style="color:#3387cc">7</span>);
    <span style="color:#3387cc">model</span>.<span style="color:#3387cc">setValueAt</span>(<span style="color:#3387cc">rec</span>.<span style="color:#3387cc">getString</span>(<span style="color:#65b042">"typenamefk"</span>), row, <span style="color:#3387cc">8</span>);
    <span style="color:#3387cc">model</span>.<span style="color:#3387cc">setValueAt</span>(<span style="color:#3387cc">rec</span>.<span style="color:#3387cc">getString</span>(<span style="color:#65b042">"methodmoneyfk"</span>), row, <span style="color:#3387cc">9</span>);
    <span style="color:#3387cc">model</span>.<span style="color:#3387cc">setValueAt</span>(<span style="color:#3387cc">rec</span>.<span style="color:#3387cc">getString</span>(<span style="color:#65b042">"calymd"</span>), row, <span style="color:#3387cc">10</span>);
    <span style="color:#3387cc">model</span>.<span style="color:#3387cc">setValueAt</span>(<span style="color:#3387cc">rec</span>.<span style="color:#3387cc">getString</span>(<span style="color:#65b042">"age"</span>), row, <span style="color:#3387cc">11</span>);
    <span style="color:#3387cc">model</span>.<span style="color:#3387cc">setValueAt</span>(<span style="color:#3387cc">rec</span>.<span style="color:#3387cc">getString</span>(<span style="color:#65b042">"money"</span>), row, <span style="color:#3387cc">12</span>);

    <span style="color:#e28964">/</span><span style="color:#e28964">/</span><span style="color:#3387cc">model</span>.<span style="color:#3387cc">setValueAt</span>(<span style="color:#3387cc">rec</span>.<span style="color:#3387cc">getFloat</span>(<span style="color:#65b042">"Budget"</span>), row, <span style="color:#3387cc">6</span>);
    <span style="color:#e28964">/</span><span style="color:#e28964">/</span><span style="color:#3387cc">model</span>.<span style="color:#3387cc">setValueAt</span>(<span style="color:#3387cc">rec</span>.<span style="color:#3387cc">getFloat</span>(<span style="color:#65b042">"Used"</span>), row, <span style="color:#3387cc">7</span>);

    row<span style="color:#e28964">+</span><span style="color:#e28964">+</span>;
    }

    <span style="color:#e28964">/</span><span style="color:#e28964">/</span> Header Font &amp; Color
    <span style="color:#3387cc">table</span>.<span style="color:#3387cc">getTableHeader</span>().setFont(new Font(<span style="color:#65b042">"SansSerif"</span>, <span style="color:#3387cc">Font</span>.<span style="color:#3387cc">BOLD</span>, <span style="color:#3387cc">12</span>));
    <span style="color:#3387cc">table</span>.<span style="color:#3387cc">getTableHeader</span>().setForeground(<span style="color:#3387cc">Color</span>.<span style="color:#3387cc">blue</span>);

    <span style="color:#e28964">/</span><span style="color:#e28964">/</span> Column Width
    <span style="color:#3387cc">table</span>.<span style="color:#3387cc">getColumnModel</span>().getColumn(<span style="color:#3387cc">0</span>).setPreferredWidth(<span style="color:#3387cc">10</span>);<span style="color:#e28964">/</span><span style="color:#e28964">/</span>CodePK
    <span style="color:#3387cc">table</span>.<span style="color:#3387cc">getColumnModel</span>().getColumn(<span style="color:#3387cc">1</span>).setPreferredWidth(<span style="color:#3387cc">10</span>);<span style="color:#e28964">/</span><span style="color:#e28964">/</span>Sex
    <span style="color:#3387cc">table</span>.<span style="color:#3387cc">getColumnModel</span>().getColumn(<span style="color:#3387cc">2</span>).setPreferredWidth(<span style="color:#3387cc">30</span>);<span style="color:#e28964">/</span><span style="color:#e28964">/</span>TitleFK
    <span style="color:#3387cc">table</span>.<span style="color:#3387cc">getColumnModel</span>().getColumn(<span style="color:#3387cc">3</span>).setPreferredWidth(<span style="color:#3387cc">120</span>);<span style="color:#e28964">/</span><span style="color:#e28964">/</span>FirstName
    <span style="color:#3387cc">table</span>.<span style="color:#3387cc">getColumnModel</span>().getColumn(<span style="color:#3387cc">4</span>).setPreferredWidth(<span style="color:#3387cc">70</span>);<span style="color:#e28964">/</span><span style="color:#e28964">/</span>IDCard
    <span style="color:#3387cc">table</span>.<span style="color:#3387cc">getColumnModel</span>().getColumn(<span style="color:#3387cc">5</span>).setPreferredWidth(<span style="color:#3387cc">20</span>);<span style="color:#e28964">/</span><span style="color:#e28964">/</span>BirthDate
    <span style="color:#3387cc">table</span>.<span style="color:#3387cc">getColumnModel</span>().getColumn(<span style="color:#3387cc">6</span>).setPreferredWidth(<span style="color:#3387cc">20</span>);<span style="color:#e28964">/</span><span style="color:#e28964">/</span>BanID
    <span style="color:#3387cc">table</span>.<span style="color:#3387cc">getColumnModel</span>().getColumn(<span style="color:#3387cc">7</span>).setPreferredWidth(<span style="color:#3387cc">10</span>);<span style="color:#e28964">/</span><span style="color:#e28964">/</span>Moo
    <span style="color:#3387cc">table</span>.<span style="color:#3387cc">getColumnModel</span>().getColumn(<span style="color:#3387cc">8</span>).setPreferredWidth(<span style="color:#3387cc">120</span>);<span style="color:#e28964">/</span><span style="color:#e28964">/</span>TypeNameFK
    <span style="color:#3387cc">table</span>.<span style="color:#3387cc">getColumnModel</span>().getColumn(<span style="color:#3387cc">9</span>).setPreferredWidth(<span style="color:#3387cc">40</span>);<span style="color:#e28964">/</span><span style="color:#e28964">/</span>MethodMoneyFK
    <span style="color:#3387cc">table</span>.<span style="color:#3387cc">getColumnModel</span>().getColumn(<span style="color:#3387cc">10</span>).setPreferredWidth(<span style="color:#3387cc">20</span>);<span style="color:#e28964">/</span><span style="color:#e28964">/</span>CalYMD
    <span style="color:#3387cc">table</span>.<span style="color:#3387cc">getColumnModel</span>().getColumn(<span style="color:#3387cc">11</span>).setPreferredWidth(<span style="color:#3387cc">10</span>);<span style="color:#e28964">/</span><span style="color:#e28964">/</span>Age
    <span style="color:#3387cc">table</span>.<span style="color:#3387cc">getColumnModel</span>().getColumn(<span style="color:#3387cc">12</span>).setPreferredWidth(<span style="color:#3387cc">20</span>);<span style="color:#e28964">/</span><span style="color:#e28964">/</span><span style="color:#99cf50">Money</span>

    <span style="color:#e28964">/</span><span style="color:#e28964">/</span> Row Height
    <span style="color:#3387cc">table</span>.<span style="color:#3387cc">setRowHeight</span>(<span style="color:#3387cc">20</span>);

    <span style="color:#e28964">/</span><span style="color:#e28964">/</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span>Count Budget<span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span>
    String com1 <span style="color:#e28964">=</span> <span style="color:#65b042">"select budget "</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"from tblsetting "</span>;

    pstmt <span style="color:#e28964">=</span> <span style="color:#3387cc">connect</span>.<span style="color:#3387cc">prepareStatement</span>(com1);<span style="color:#e28964">/</span><span style="color:#e28964">/</span>new
    rec <span style="color:#e28964">=</span> <span style="color:#3387cc">pstmt</span>.<span style="color:#3387cc">executeQuery</span>();<span style="color:#e28964">/</span><span style="color:#e28964">/</span>new

    if (<span style="color:#3387cc">rec</span>.<span style="color:#3387cc">next</span>()) {
    <span style="color:#99cf50">int</span> maxID <span style="color:#e28964">=</span> <span style="color:#3387cc">rec</span>.<span style="color:#3387cc">getInt</span>(<span style="color:#3387cc">1</span>);

    <span style="color:#3387cc">txtBudget</span>.<span style="color:#3387cc">setText</span>(<span style="color:#3387cc">String</span>.<span style="color:#3387cc">valueOf</span>(maxID));
    }

    <span style="color:#e28964">/</span><span style="color:#e28964">/</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span>Calculate<span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span>
    Calculate();
    <span style="color:#e28964">/</span><span style="color:#e28964">/</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span><span style="color:#e28964">=</span>
    <span style="color:#3387cc">pstmt</span>.<span style="color:#3387cc">close</span>();
    <span style="color:#3387cc">connect</span>.<span style="color:#3387cc">close</span>();
    }catch(SQLException ex){
    <span style="color:#3387cc">JOptionPane</span>.<span style="color:#3387cc">showMessageDialog</span>(<span style="color:#e28964">null</span>, <span style="color:#65b042">"ไม่สามารถแสดงข้อมูลได้!\n"</span><span style="color:#e28964">+</span><span style="color:#3387cc">ex</span>.<span style="color:#3387cc">getMessage</span>());
    }
    }
    </pre>
     
  4. ledphong

    ledphong เป็นที่รู้จักกันดี

    วันที่สมัครสมาชิก:
    28 มีนาคม 2009
    โพสต์:
    1,425
    ค่าพลัง:
    +165
    Code ::Day Month(MariaDB)
    ==================
    <pre style="background:#000;color:#f8f8f8"><span style="color:#e28964">private</span> <span style="color:#dad085">void</span> showdata() {
    <span style="color:#dad085">try</span>{
    Class.forName(<span style="color:#65b042">"org.mariadb.jdbc.Driver"</span>);
    <span style="color:#dad085">connect</span> <span style="color:#e28964">=</span> DriverManager.getConnection(<span style="color:#65b042">"jdbc:mariadb://localhost/dbolder"</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"?user=root&amp;password=gomplayer"</span>);

    <span style="color:#99cf50">String</span> sql <span style="color:#e28964">=</span> <span style="color:#65b042">"SELECT tblolder.CodePK,tblolder.SexFK,"</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"tblolder.TitleFK,CONCAT_WS(' ',tblolder.FirstName,tblolder.LastName) As FirstName,"</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"tblolder.IDCard,DATE_FORMAT(DATE_ADD(tblolder.BirthDate, INTERVAL 543 YEAR),'%d/%m/%Y') AS BirthDate,tblolder.BanID ,"</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"tblolder.Moo,tblolder.TypeNameFK,"</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"tblolder.MethodMoneyFK ,DATE_FORMAT(DATE_ADD(tblsetting.CalYMD, INTERVAL 543 YEAR),'%d/%m/%Y') AS CalYMD ,"</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"TRUNCATE((DATEDIFF(tblsetting.CalYMD,tblolder.BirthDate)/365.25),0) as Age ,"</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"case when (TRUNCATE((DATEDIFF(tblsetting.CalYMD,tblolder.BirthDate)/365.25),0)) >= 90 then '1000.00' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE((DATEDIFF(tblsetting.CalYMD,tblolder.BirthDate)/365.25),0)) >= 80 then '800.00' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE((DATEDIFF(tblsetting.CalYMD,tblolder.BirthDate)/365.25),0)) >= 70 then '700.00' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE((DATEDIFF(tblsetting.CalYMD,tblolder.BirthDate)/365.25),0)) >= 60 then '600.00' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"else '0.00' END as Money , "</span><span style="color:#e28964">+</span>
    <span style="color:#aeaeae;font-style:italic">//"(case when (TRUNCATE((DATEDIFF(tblsetting.CalYMD,tblolder.BirthDate)/365.25),0)) >= 90 then '1000.00' " +</span>
    <span style="color:#aeaeae;font-style:italic">//"when (TRUNCATE((DATEDIFF(tblsetting.CalYMD,tblolder.BirthDate)/365.25),0)) >= 80 then '800.00' " +</span>
    <span style="color:#aeaeae;font-style:italic">//"when (TRUNCATE((DATEDIFF(tblsetting.CalYMD,tblolder.BirthDate)/365.25),0)) >= 70 then '700.00' " +</span>
    <span style="color:#aeaeae;font-style:italic">//"when (TRUNCATE((DATEDIFF(tblsetting.CalYMD,tblolder.BirthDate)/365.25),0)) >= 60 then '600.00' " +</span>
    <span style="color:#aeaeae;font-style:italic">//"else '0.00' END)" +</span>
    <span style="color:#aeaeae;font-style:italic">//"+MoneyAccess As MoneyTotal " +</span>
    <span style="color:#65b042">"MoneyAccess As MoneyTotal,"</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"(case when (TRUNCATE(DAYOFWEEK(tblolder.BirthDate),0)) = 1 then 'อาทิตย์' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(DAYOFWEEK(tblolder.BirthDate),0)) = 2 then 'จันทร์' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(DAYOFWEEK(tblolder.BirthDate),0)) = 3 then 'อังคาร' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(DAYOFWEEK(tblolder.BirthDate),0)) = 4 then 'พุธ' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(DAYOFWEEK(tblolder.BirthDate),0)) = 5 then 'พฤหัสบดี' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(DAYOFWEEK(tblolder.BirthDate),0)) = 6 then 'ศุกร์' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"else 'เสาร์' END) As StrDayName, "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"(case when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 1 then 'มกราคม' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 2 then 'กุมภาพันธ์' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 3 then 'มีนาคม' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 4 then 'เมษายน' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 5 then 'พฤษภาคม' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 6 then 'มิถุนายน' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 7 then 'กรกฎาคม' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 8 then 'สิงหาคม' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 9 then 'กันยายน' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 10 then 'ตุลาคม' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 11 then 'พฤศจิกายน' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"else 'ธันวาคม' END) As StrMonthName "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"FROM tblolder Left Join tblsetting "</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"ON tblolder.Interaction = tblsetting.Interaction "</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"WHERE "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"tblolder.MethodMoneyFK like '%"</span> <span style="color:#e28964">+</span> txtSearchMethodMoney.<span style="color:#dad085">getText</span>() <span style="color:#e28964">+</span> <span style="color:#65b042">"%' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"AND tblolder.Moo like '%"</span> <span style="color:#e28964">+</span> txtSearchMoo.<span style="color:#dad085">getText</span>() <span style="color:#e28964">+</span> <span style="color:#65b042">"%' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"AND tblolder.FirstName like '%"</span> <span style="color:#e28964">+</span> txtSearchFirstName.<span style="color:#dad085">getText</span>() <span style="color:#e28964">+</span> <span style="color:#65b042">"%' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"AND (case when (TRUNCATE(DAYOFWEEK(tblolder.BirthDate),0)) = 1 then 'อาทิตย์' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(DAYOFWEEK(tblolder.BirthDate),0)) = 2 then 'จันทร์' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(DAYOFWEEK(tblolder.BirthDate),0)) = 3 then 'อังคาร' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(DAYOFWEEK(tblolder.BirthDate),0)) = 4 then 'พุธ' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(DAYOFWEEK(tblolder.BirthDate),0)) = 5 then 'พฤหัสบดี' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(DAYOFWEEK(tblolder.BirthDate),0)) = 6 then 'ศุกร์' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"else 'เสาร์' END) like '%"</span> <span style="color:#e28964">+</span> txtDayName.<span style="color:#dad085">getText</span>() <span style="color:#e28964">+</span> <span style="color:#65b042">"%' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"AND (case when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 1 then 'มกราคม' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 2 then 'กุมภาพันธ์' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 3 then 'มีนาคม' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 4 then 'เมษายน' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 5 then 'พฤษภาคม' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 6 then 'มิถุนายน' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 7 then 'กรกฎาคม' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 8 then 'สิงหาคม' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 9 then 'กันยายน' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 10 then 'ตุลาคม' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"when (TRUNCATE(MONTH(tblolder.BirthDate),0)) = 11 then 'พฤศจิกายน' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"else 'ธันวาคม' END) like '%"</span> <span style="color:#e28964">+</span> txtMonthName.<span style="color:#dad085">getText</span>() <span style="color:#e28964">+</span> <span style="color:#65b042">"%' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"AND tblolder.TypeNameFK like '%"</span> <span style="color:#e28964">+</span> txtTypeName.<span style="color:#dad085">getText</span>() <span style="color:#e28964">+</span> <span style="color:#65b042">"%' "</span> <span style="color:#e28964">+</span>
    <span style="color:#65b042">"ORDER BY tblolder.Moo,tblolder.BanID ASC"</span>;

    pstmt <span style="color:#e28964">=</span> <span style="color:#dad085">connect</span>.prepareStatement(sql);<span style="color:#aeaeae;font-style:italic">//new</span>
    rec <span style="color:#e28964">=</span> pstmt.executeQuery();<span style="color:#aeaeae;font-style:italic">//new</span>

    <span style="color:#dad085">int</span> row <span style="color:#e28964">=</span> <span style="color:#3387cc">0</span>;
    <span style="color:#e28964">while</span>((rec<span style="color:#e28964">!</span><span style="color:#e28964">=</span><span style="color:#3387cc">null</span>) <span style="color:#e28964">&amp;</span><span style="color:#e28964">&amp;</span> (rec.<span style="color:#dad085">next</span>()))
    {
    model.addRow(<span style="color:#e28964">new</span> <span style="color:#9b859d">Object</span>[<span style="color:#3387cc">0</span>]);
    model.setValueAt(rec.getString(<span style="color:#65b042">"CodePK"</span>), row, <span style="color:#3387cc">0</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"SexFK"</span>), row, <span style="color:#3387cc">1</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"TitleFK"</span>), row, <span style="color:#3387cc">2</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"FirstName"</span>), row, <span style="color:#3387cc">3</span>);
    <span style="color:#aeaeae;font-style:italic">//model.setValueAt(rec.getString("LastName"), row, 4);</span>
    model.setValueAt(rec.getString(<span style="color:#65b042">"IDCard"</span>), row, <span style="color:#3387cc">4</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"BirthDate"</span>), row, <span style="color:#3387cc">5</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"BanID"</span>), row, <span style="color:#3387cc">6</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"Moo"</span>), row, <span style="color:#3387cc">7</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"TypeNameFK"</span>), row, <span style="color:#3387cc">8</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"MethodMoneyFK"</span>), row, <span style="color:#3387cc">9</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"CalYMD"</span>), row, <span style="color:#3387cc">10</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"Age"</span>), row, <span style="color:#3387cc">11</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"Money"</span>), row, <span style="color:#3387cc">12</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"MoneyTotal"</span>), row, <span style="color:#3387cc">13</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"StrDayName"</span>), row, <span style="color:#3387cc">14</span>);
    model.setValueAt(rec.getString(<span style="color:#65b042">"StrMonthName"</span>), row, <span style="color:#3387cc">15</span>);

    <span style="color:#aeaeae;font-style:italic">//model.setValueAt(rec.getFloat("Budget"), row, 6);</span>
    <span style="color:#aeaeae;font-style:italic">//model.setValueAt(rec.getFloat("Used"), row, 7);</span>

    row<span style="color:#e28964">+</span><span style="color:#e28964">+</span>;
    }

    <span style="color:#aeaeae;font-style:italic">// Header Font &amp; Color</span>
    table.getTableHeader().setFont(<span style="color:#e28964">new</span> Font(<span style="color:#65b042">"SansSerif"</span>, Font.BOLD, <span style="color:#3387cc">12</span>));
    table.getTableHeader().setForeground(<span style="color:#9b859d">Color</span>.blue);

    <span style="color:#aeaeae;font-style:italic">// Column Width</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">0</span>).setPreferredWidth(<span style="color:#3387cc">10</span>);<span style="color:#aeaeae;font-style:italic">//CodePK</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">1</span>).setPreferredWidth(<span style="color:#3387cc">10</span>);<span style="color:#aeaeae;font-style:italic">//Sex</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">2</span>).setPreferredWidth(<span style="color:#3387cc">10</span>);<span style="color:#aeaeae;font-style:italic">//TitleFK</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">3</span>).setPreferredWidth(<span style="color:#3387cc">70</span>);<span style="color:#aeaeae;font-style:italic">//FirstName</span>
    <span style="color:#aeaeae;font-style:italic">//table.getColumnModel().getColumn(4).setPreferredWidth(70);//LastName</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">4</span>).setPreferredWidth(<span style="color:#3387cc">70</span>);<span style="color:#aeaeae;font-style:italic">//IDCard</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">5</span>).setPreferredWidth(<span style="color:#3387cc">20</span>);<span style="color:#aeaeae;font-style:italic">//BirthDate</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">6</span>).setPreferredWidth(<span style="color:#3387cc">10</span>);<span style="color:#aeaeae;font-style:italic">//BanID</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">7</span>).setPreferredWidth(<span style="color:#3387cc">10</span>);<span style="color:#aeaeae;font-style:italic">//Moo</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">8</span>).setPreferredWidth(<span style="color:#3387cc">100</span>);<span style="color:#aeaeae;font-style:italic">//TypeNameFK</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">9</span>).setPreferredWidth(<span style="color:#3387cc">10</span>);<span style="color:#aeaeae;font-style:italic">//MethodMoneyFK</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">10</span>).setPreferredWidth(<span style="color:#3387cc">20</span>);<span style="color:#aeaeae;font-style:italic">//CalYMD</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">11</span>).setPreferredWidth(<span style="color:#3387cc">10</span>);<span style="color:#aeaeae;font-style:italic">//Age</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">12</span>).setPreferredWidth(<span style="color:#3387cc">10</span>);<span style="color:#aeaeae;font-style:italic">//Money</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">13</span>).setPreferredWidth(<span style="color:#3387cc">10</span>);<span style="color:#aeaeae;font-style:italic">//MoneyAccess</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">14</span>).setPreferredWidth(<span style="color:#3387cc">10</span>);<span style="color:#aeaeae;font-style:italic">//StrDayName</span>
    table.getColumnModel().getColumn(<span style="color:#3387cc">15</span>).setPreferredWidth(<span style="color:#3387cc">10</span>);<span style="color:#aeaeae;font-style:italic">//StrMonthName</span>

    <span style="color:#aeaeae;font-style:italic">// Row Height</span>
    table.<span style="color:#dad085">setRowHeight</span>(<span style="color:#3387cc">20</span>);

    <span style="color:#aeaeae;font-style:italic">//======Count Budget=============</span>
    <span style="color:#99cf50">String</span> com1 <span style="color:#e28964">=</span> <span style="color:#65b042">"SELECT Budget "</span><span style="color:#e28964">+</span>
    <span style="color:#65b042">"FROM tblsetting "</span>;

    pstmt <span style="color:#e28964">=</span> <span style="color:#dad085">connect</span>.prepareStatement(com1);<span style="color:#aeaeae;font-style:italic">//new</span>
    rec <span style="color:#e28964">=</span> pstmt.executeQuery();<span style="color:#aeaeae;font-style:italic">//new</span>

    <span style="color:#e28964">if</span> (rec.<span style="color:#dad085">next</span>()) {
    <span style="color:#dad085">int</span> maxID <span style="color:#e28964">=</span> rec.getInt(<span style="color:#3387cc">1</span>);

    txtBudget.setText(<span style="color:#99cf50">String</span>.<span style="color:#dad085">valueOf</span>(maxID));
    }

    <span style="color:#aeaeae;font-style:italic">//=======Calculate================</span>
    Calculate();
    <span style="color:#aeaeae;font-style:italic">//================================</span>
    pstmt.<span style="color:#dad085">close</span>();
    <span style="color:#dad085">connect</span>.<span style="color:#dad085">close</span>();
    }<span style="color:#dad085">catch</span>(ClassNotFoundException | SQLException ex){
    JOptionPane.showMessageDialog(<span style="color:#3387cc">null</span>, <span style="color:#65b042">"ไม่สามารถแสดงข้อมูลได้!<span style="color:#ddf2a4">\n</span>"</span><span style="color:#e28964">+</span>ex.<span style="color:#dad085">getMessage</span>());
    }
    }
    </pre>
     

แชร์หน้านี้

Loading...